Proc Transpose questions

Solph — Fri, 21 Sep 2012 16:14:23 GMT

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.

Re: Proc Transpose questions

data_null__ — Fri, 21 Sep 2012 16:55:59 GMT

proc sort data=aa(keep=year) out=years nodupkey; 
   by year;
   run; 
data aaV / view=aaV;
   set years aa;
   run; 
proc transpose data=aaV
    out = bb(drop=_Name_ where=(not missing(id))) 
    prefix = yr;
    id year;
    by id;
    var year;
    idlabel year;
    run; 

Re: Proc Transpose questions

PGStats — Fri, 21 Sep 2012 18:34:09 GMT

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;

Re: Proc Transpose questions

Solph — Fri, 21 Sep 2012 21:55:55 GMT

Thanks a lot. They worked perfectly and in such simple steps.

topic Re: Proc Transpose questions in SAS Procedures

Proc Transpose questions

Re: Proc Transpose questions

Re: Proc Transpose questions

Re: Proc Transpose questions