Generating missing values in a Table

-

USUBJID VISITNUMBER
10011001 1
10011001 7
10011002 1
10011003 1

In the above example, the usubjid (10011002 and 10011003) has visitnumber 7 missing. The proc transpose statement helps in generating these missing values.

** PROC TRANSPOSE FOR GENERATING MISSING VALUES **;

proc transpose data = d1 out = d2 prefix = v;
by usubjid treatnc ;
id vis;
*vis is a character variable which contains visitnumber (1,7);
var vala valb valc vald vale valf valg valh vali valj;
run;

When transposed the data becomes:
USUBJID V1 V7
10011001 -0 --0
10011002 -0 --''
10011003 -0 --''

** RE(cursive) TRANSPOSE: TO BRING THE DATA BACK TO ITS SHAPE **;

proc transpose data = d2 out = d3;
by usubjid treatnc ;
id _name_;
*all values vala - valj becomes identifier again;
var v1 v7;
run;

The data would now look like:
USUBJID _name_ vala
10011001 -v1-------0
10011001 -v7-------0
10011002 -v1-------0
10011002 -v7-------''
10011003 -v1-------0
10011003 -v7-------''

proc sort data = d3;
by usubjid treatnc _name_;
run;

** BRING THE VARIABLES ONE AFTER THE OTHER FOR FREQ **;

proc transpose data = d3(rename = (_name_ = name)) out = d4;
by usubjid treatnc name;
var vala valb valc vald vale valf valg valh vali valj;
run;

-