In PROC TRANSPOSE,
1. can I ask SAS to sort the order of the variables based on the value of the transpose variable when it has a number component (e.g. yr2003 yr2004 yr2005, etc.), instead of based on the first encounter of the values?
2. when transposed, can those values be transposed as different values (say 1) instead of the actual values (e.g. year value 2003, 2004, etc.)
The data example and the specific questions are as the following.
data aa; input id $ year;
datalines;
a1 2005
a1 2006
a1 2008
a2 2004
a2 2005
a2 2007
a2 2008
a3 2004
a3 2005
a3 2006
a3 2007
a3 2008
a3 2009
a4 2003
a4 2004
a4 2005
a4 2006
a4 2007
a4 2008
a4 2009
a5 2003
a5 2004
a5 2005
a5 2006
;
proc sort data =aa; by id year; run;
proc transpose data=aa
out = bb (drop=_Name_ ) prefix = yr;
id year;
by id;
var year;
idlabel year;
run;
proc print; run;
The output would look like this:
id yr2005 yr2006 yr2008 yr2004 yr2007 yr2009 yr2003
a1 2005 2006 2008 . . . .
a2 2005 . 2008 2004 2007 . .
a3 2005 2006 2008 2004 2007 2009 .
a4 2005 2006 2008 2004 2007 2009 2003
a5 2005 2006 . 2004 . . 2003
Questions.
1. Ultimately I'd like to have the year variables read across in ascending order from small to large
1a. Can I do it in transpose?
1b. If I have to use retain to reorder variables, any easy way to do it without having to have list all year variables? I may have > > 80 years span, so it's 80 year variables;
data want;
retain id yr2003 yr2004 yr2005 yr2006 yr2007 yr2008 yr2009;
set bb;
run;
2. Is it possible to ask SAS in PROC TRANSPOSE to transpose the year value as 1, not as the actual year value?
(I know I could use array to assign all variables for values >0 as 1. Just curious if I could do in one step in proc transpose
id yr2005 yr2006 yr2008 yr2004 yr2007 yr2009 yr2003
a1 1 1 1 . . . .
a2 1 . 1 1 1 . .
etc.
Many thanks.
Not a single step, but close...
data aaa / view=aaa; set aa; var=1; run;
proc transpose data=aaa out=bb(drop=_name_) prefix=yr;
var var;
by id;
id year;
run;
proc print noobs; run;
PG
Thanks a lot. They worked perfectly and in such simple steps.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.