data long;
input enrolid1 date1 : mmddyy10. test result location;
format date1 mmddyy10.;
datalines;
4 5/5/2009 blood 65 inp
4 5/5/2009 colon Y inp
4 5/5/2009 stomach Y inp
4 5/6/2009 head N inp
4 5/6/2009 blood 55 out
4 5/6/2009 colon N out
4 5/6/2009 stomach N out
4 5/6/2009 neck Y out
4 5/6/2009 ZY Y out
5 5/5/2009 blood 55 inp
5 5/5/2009 add Y inp
5/5/5/2009 mst N inp;
run;
I have the above data, I need to transpose it to a wide format by listing each "test" for that specific date. Variable location is fixed for each patient during a specific date.
Output I am looking for
enrolid1 date1 blood colon stomach head neck ZY add mst location
4 5/5/2009 65 Y Y . . . . . inp
4 5/6/2009 55 N N N Y Y . . out
5 5/5/2009 55 . . . . . Y N inp
data long;
input enrolid1 date1 : mmddyy10. test $ result $ location $;
format date1 mmddyy10.;
datalines;
4 5/5/2009 blood 65 inp
4 5/5/2009 colon Y inp
4 5/5/2009 stomach Y inp
4 5/6/2009 head N inp
4 5/6/2009 blood 55 out
4 5/6/2009 colon N out
4 5/6/2009 stomach N out
4 5/6/2009 neck Y out
4 5/6/2009 ZY Y out
5 5/5/2009 blood 55 inp
5 5/5/2009 add Y inp
5 5/5/2009 mst N inp
;
run;
proc transpose data=long out=long_t;
by enrolid1 date1;
id test;
var result location;
run;
data want;
merge long_t(where=(_name_='result'))
long_t(where=(_name_='location') keep=enrolid1 date1 _name_ blood
rename=(blood=location));
by enrolid1 date1;
drop _name_;
run;
data long;
input enrolid1 date1 : mmddyy10. test $ result $ location $;
format date1 mmddyy10.;
datalines;
4 5/5/2009 blood 65 inp
4 5/5/2009 colon Y inp
4 5/5/2009 stomach Y inp
4 5/6/2009 head N inp
4 5/6/2009 blood 55 out
4 5/6/2009 colon N out
4 5/6/2009 stomach N out
4 5/6/2009 neck Y out
4 5/6/2009 ZY Y out
5 5/5/2009 blood 55 inp
5 5/5/2009 add Y inp
5 5/5/2009 mst N inp
;
run;
proc transpose data=long out=long_t;
by enrolid1 date1;
id test;
var result location;
run;
data want;
merge long_t(where=(_name_='result'))
long_t(where=(_name_='location') keep=enrolid1 date1 _name_ blood
rename=(blood=location));
by enrolid1 date1;
drop _name_;
run;
Thank you Piage! very helpful!
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.