topic Re: transpose into wide format in SAS Programming

transpose into wide format

lillymaginta — Fri, 28 Jun 2019 14:30:34 GMT

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

Re: transpose into wide format

PaigeMiller — Fri, 28 Jun 2019 14:42:37 GMT

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;

Re: transpose into wide format

lillymaginta — Fri, 28 Jun 2019 14:49:13 GMT

Thank you Piage! very helpful!