Collapsing records for vital signs

[kalbo]

Hi, I have a vital signs data set with multiple rows per subject/visit/timepoint. I would like to collapse the data so there is one row per subject/visit/timepoint.

Have:

SUBJECT VISIT TIMEPOINT  TEMP    SYSBP DIABP 

101             C1        PRE                  1              .             .

101             C1        PRE                  .               21         43

101             C2       POST                .             2 5        46

102            C1        PRE                  1             22          32

102            C1       POST                .              56          .

102            C1       POST               2              .              25

102           C2       POST               5               55          54

Want:

SUBJECT VISIT TIMEPOINT  TEMP    SYSBP DIABP 

101             C1        PRE                 1               21         43

101             C2       POST                .             2 5        46

102            C1        PRE                  1             22          32

102            C1       POST                2           56           25

102           C2       POST               5             55          54

[PaigeMiller]

Assuming the pattern you show is always the pattern in the real data, with in each column only one number and the rest are missing for each subject/visit/timepoint, then this is a perfect job for PROC SUMMARY

proc summary data=have nway;
    class subject visit timepoint;
    var temp sysbp diabp;
    output out=want mean=;
run;

[Tom · Accepted solution]

If you want the variables to have the LAST non missing value per group then use the UPDATE statement to treat the records as transactions.

data want;
  update have(obs=0) have;
  by SUBJECT VISIT TIMEPOINT  ;
run;

The UPDATE statement needs two dataset.  The original data and the transaction records.  The original data needs to have one observation per BY group.  But you can have multiple transaction records per BY group.  So just use an empty version of the data (by using the OBS=0 dataset option) as the original data and all of the observations as transactions.