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
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.