I am trying to transpose a dataset that looks like
SUBJ VISIT SITE1 SITE2 SITE3
1 1 3.5 4.5 2.5
1 2 3.6 4.6 2.2
2 1 3.4 3.2 2.1
2 2 4.5 5.2 4.4
And I want it to look like
SUBJ SITE1.1 SITE2.1 SITE3.1 SITE1.2 SITE2.2 SITE3.2
1 3.5 4.5 2.5 3.6 4.6 2.2
2 3.4 3.2 2.1 4.5 5.2 4.4
I want keep the existing varible names, but add .(visit) to the name to identify the visit.
I can only find ways to do one variable at a time, but need to transpose poses 30 variables with several time points.
Many thinks in advance.
John.
The simplest way is using IDGROUP.
Or for big table, could try MERGE skill.
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input SUBJ VISIT SITE1 SITE2 SITE3 ;
cards;
1 1 3.5 4.5 2.5
1 2 3.6 4.6 2.2
2 1 3.4 3.2 2.1
2 2 4.5 5.2 4.4
;
proc sql noprint;
select max(visit) into : max from have;
quit;
proc summary data=have ;
by subj;
output out=want idgroup(out[&max] (SITE1 SITE2 SITE3)=);
run;
I suggest using the transpose macro you can find at: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
after downloading and running it, you would only have to run something like:
data have; input SUBJ VISIT SITE1 SITE2 SITE3 test1 test2 test3; cards; 1 1 3.5 4.5 2.5 1 2 3 1 2 3.6 4.6 2.2 3 2 1 2 1 3.4 3.2 2.1 4 5 6 2 2 4.5 5.2 4.4 6 5 4 ; %transpose(data=have, out=want, by=subj, id=visit, delimiter=_, var=site1--test3)
Art, CEO, AnalystFinder.com
SAS variable names cannot contain a dot (.) but we could use an underscore. The following transposes for 5 visits using the DATA step. This could be cleaned up and made more flexible, but it should do what you requested.
data have;
input SUBJ VISIT SITE1 SITE2 SITE3;
datalines;
1 1 3.5 4.5 2.5
1 2 3.6 4.6 2.2
2 1 3.4 3.2 2.1
2 2 4.5 5.2 4.4
run;
data want(keep=subj site1_: site2_: site3_:);
set have;
by subj;
array st1 {5} site1_1 - site1_5;
array st2 {5} site2_1 - site2_5;
array st3 {5} site3_1 - site3_5;
retain site1: site2: site3: .;
if first.subj then call missing(of st1{*} st2{*} st3{*});
st1{visit} = site1;
st2{visit} = site2;
st3{visit} = site3;
if last.subj then output want;
run;
For your arrays are you more likely to do your analysis by site or time period? Which ever one is more common should be the first prefix in your variable. SAS can use prefixes to reference a variable list, which is why you'd want to factor this into your decision making.
The simplest way is using IDGROUP.
Or for big table, could try MERGE skill.
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input SUBJ VISIT SITE1 SITE2 SITE3 ;
cards;
1 1 3.5 4.5 2.5
1 2 3.6 4.6 2.2
2 1 3.4 3.2 2.1
2 2 4.5 5.2 4.4
;
proc sql noprint;
select max(visit) into : max from have;
quit;
proc summary data=have ;
by subj;
output out=want idgroup(out[&max] (SITE1 SITE2 SITE3)=);
run;
This works exactly as I was looking for. I was used to doing this in another program but "transpose" function wasn't working for me.
Many thanks.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.