topic Re: Collapsing records for vital signs in SAS Programming

Collapsing records for vital signs

kalbo — Thu, 26 Sep 2024 10:20:48 GMT

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

Re: Collapsing records for vital signs

PaigeMiller — Thu, 26 Sep 2024 10:40:52 GMT

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;

Re: Collapsing records for vital signs

Tom — Thu, 26 Sep 2024 12:55:22 GMT

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.