Proc Transpose questions

[Solph]

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.

[data_null__]

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;

[PGStats]

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

[Solph]

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