Need transpose output sorted

[bfolzblu]

Hi all, I have an EG project (EG 4.1.0.1019) that uses the transpose procedure to take data for a student's Financial Aid year and put the values of the aid in one obs per student id.

We then need to output the results to Excel and the end-user wants the Aid Years listed in order.

My question - is there a way I can I sort the data when the # and names of the fields created will vary after the transpose step?

Data Prior to Transpose (variables are: ID, AidYear, Amt)

A00008052 2005 3500
A00008052 2006 2334
A00016857 2006 1092
A00016857 2007 1750
A00016857 2008 2250
A01013424 2008 2847
A02010379 2010 5000
A02015865 2008 3500
A02015865 2009 7500
A02015865 2010 3500
A03008406 2005 1313
A03008406 2006 1152
A03008406 2007 1616
A03008406 2009 1500
A03008406 2010 2250
A03009354 2005 1750
A03009354 2006 531
A03009354 2007 3500
A03009354 2008 3500
A03009354 2009 4500
A03013216 2010 4500
A04000477 2009 5000
A04000477 2010 2000
A04001905 2008 7009.45
A04001905 2009 3170.34
A04001905 2010 9820
A04026407 2007 1750
A05014451 2007 2831
A05018049 2010 4500
A05019773 2006 1300
A05019773 2011 2594
A05019864 2008 3500
A05022835 2006 1313
A05022835 2007 1313
A05022835 2008 1750
A05022835 2009 4000
A05027388 2007 2625
A06043764 2009 3000
A06053193 2008 1327
A06053193 2009 600
A06055123 2009 1500
A06056089 2008 3000
A06057236 2007 2573
A06065387 2009 3500
A06069363 2008 3500
A06069363 2009 3500
A07004468 2010 2250
A07005176 2008 3000
A07013253 2010 10500
A07013253 2011 7500
A07020324 2010 5500
A07020324 2011 7500
A07021850 2009 4000
A07021850 2010 4000
A08009623 2009 1416
A08009623 2009 1416
A08012775 2010 2000
A08023624 2009 4400
A08023624 2010 7500
A08047698 2009 3250
A08047698 2010 2000
A09000340 2011 5500
A09006636 2010 6000
A09013749 2010 2250
A87014437 2009 2500
A94001559 2008 7500
A94001559 2008 7500
A94001559 2009 5500
A94001559 2009 5500
A94001559 2010 4500
A94001559 2010 4500
A94001559 2011 9427
A94001559 2011 9427
A98004773 2000 2520
A98008204 2007 2625
A98008204 2008 4500
A98013857 2000 5807.04
A98013857 2001 3395
A98013857 2002 3500
A98013857 2003 1097
A99006090 2002 5500
A99006090 2003 3284
A99006090 2004 5341
A99006090 2010 10500
B01006402 2002 2625
B01006402 2004 1210
B02005510 2004 2625
B02005510 2008 1500
B02005510 2009 1500
B02005510 2010 1500
B03000809 2009 5500
B03000809 2010 3984
B04005526 2005 3500
B04005526 2009 2250
B04005526 2010 2250
B04005526 2011 5500
B05009832 2009 4500
B05009832 2010 4500
B06022503 2009 1259
B07001076 2008 4250
B08004772 2010 4000
B08015067 2010 4500
B08015067 2011 7500
B98007560 1999 2572.52
B98007560 2000 6360
B98007560 2001 1091.25

Data After Transpose (variables are: ID, 2005, 2006, 2007, 2008, 2010, 2009, 2011, 2000, 2001, 2002, 2003, 2004, 1999)

A00008052 3500 2334 . . . . . . . . . . .
A00016857 . 1092 1750 2250 . . . . . . . . .
A01013424 . . . 2847 . . . . . . . . .
A02010379 . . . . 5000 . . . . . . . .
A02015865 . . . 3500 3500 7500 . . . . . . .
A03008406 1313 1152 1616 . 2250 1500 . . . . . . .
A03009354 1750 531 3500 3500 . 4500 . . . . . . .
A03013216 . . . . 4500 . . . . . . . .
A04000477 . . . . 2000 5000 . . . . . . .
A04001905 . . . 7009.45 9820 3170.34 . . . . . . .
A04026407 . . 1750 . . . . . . . . . .
A05014451 . . 2831 . . . . . . . . . .
A05018049 . . . . 4500 . . . . . . . .
A05019773 . 1300 . . . . 2594 . . . . . .
A05019864 . . . 3500 . . . . . . . . .
A05022835 . 1313 1313 1750 . 4000 . . . . . . .
A05027388 . . 2625 . . . . . . . . . .
A06043764 . . . . . 3000 . . . . . . .
A06053193 . . . 1327 . 600 . . . . . . .
A06055123 . . . . . 1500 . . . . . . .
A06056089 . . . 3000 . . . . . . . . .
A06057236 . . 2573 . . . . . . . . . .
A06065387 . . . . . 3500 . . . . . . .
A06069363 . . . 3500 . 3500 . . . . . . .
A07004468 . . . . 2250 . . . . . . . .
A07005176 . . . 3000 . . . . . . . . .
A07013253 . . . . 10500 . 7500 . . . . . .
A07020324 . . . . 5500 . 7500 . . . . . .
A07021850 . . . . 4000 4000 . . . . . . .
A08009623 . . . . . 1416 . . . . . . .
A08009623 . . . . . 1416 . . . . . . .
A08012775 . . . . 2000 . . . . . . . .
A08023624 . . . . 7500 4400 . . . . . . .
A08047698 . . . . 2000 3250 . . . . . . .
A09000340 . . . . . . 5500 . . . . . .
A09006636 . . . . 6000 . . . . . . . .
A09013749 . . . . 2250 . . . . . . . .
A87014437 . . . . . 2500 . . . . . . .
A94001559 . . . 7500 4500 5500 9427 . . . . . .
A94001559 . . . 7500 4500 5500 9427 . . . . . .
A98004773 . . . . . . . 2520 . . . . .
A98008204 . . 2625 4500 . . . . . . . . .
A98013857 . . . . . . . 5807.04 3395 3500 1097 . .
A99006090 . . . . 10500 . . . . 5500 3284 5341 .
B01006402 . . . . . . . . . 2625 . 1210 .
B02005510 . . . 1500 1500 1500 . . . . . 2625 .
B03000809 . . . . 3984 5500 . . . . . . .
B04005526 3500 . . . 2250 2250 5500 . . . . . .
B05009832 . . . . 4500 4500 . . . . . . .
B06022503 . . . . . 1259 . . . . . . .
B07001076 . . . 4250 . . . . . . . . .
B08004772 . . . . 4000 . . . . . . . .
B08015067 . . . . 4500 . 7500 . . . . . .
B98007560 . . . . . . . 6360 1091.25 . . . 2572.52

Thanks in advance for your thoughts!

Barbara

[data_null__ · Accepted solution]

One way is to create a dummy ID with all the years.  You also need to address the problem of duplicate years in your data.

data have;

   input id :$9. year amt;

   cards;

A00008052 2005 3500

A00008052 2006 2334

A00016857 2006 1092

A00016857 2007 1750

A00016857 2008 2250

A01013424 2008 2847

A02010379 2010 5000

A02015865 2008 3500

A02015865 2009 7500

A02015865 2010 3500

A03008406 2005 1313

A03008406 2006 1152

A03008406 2007 1616

A03008406 2009 1500

A03008406 2010 2250

A03009354 2005 1750

A03009354 2006 531

A03009354 2007 3500

A03009354 2008 3500

A03009354 2009 4500

A03013216 2010 4500

A04000477 2009 5000

A04000477 2010 2000

A04001905 2008 7009.45

A04001905 2009 3170.34

A04001905 2010 9820

A04026407 2007 1750

A05014451 2007 2831

A05018049 2010 4500

A05019773 2006 1300

A05019773 2011 2594

A05019864 2008 3500

A05022835 2006 1313

A05022835 2007 1313

A05022835 2008 1750

A05022835 2009 4000

A05027388 2007 2625

A06043764 2009 3000

A06053193 2008 1327

A06053193 2009 600

A06055123 2009 1500

A06056089 2008 3000

A06057236 2007 2573

A06065387 2009 3500

A06069363 2008 3500

A06069363 2009 3500

A07004468 2010 2250

A07005176 2008 3000

A07013253 2010 10500

A07013253 2011 7500

A07020324 2010 5500

A07020324 2011 7500

A07021850 2009 4000

A07021850 2010 4000

A08009623 2009 1416

A08009623 2009 1416

A08012775 2010 2000

A08023624 2009 4400

A08023624 2010 7500

A08047698 2009 3250

A08047698 2010 2000

A09000340 2011 5500

A09006636 2010 6000

A09013749 2010 2250

A87014437 2009 2500

A94001559 2008 7500

A94001559 2008 7500

A94001559 2009 5500

A94001559 2009 5500

A94001559 2010 4500

A94001559 2010 4500

A94001559 2011 9427

A94001559 2011 9427

A98004773 2000 2520

A98008204 2007 2625

A98008204 2008 4500

A98013857 2000 5807.04

A98013857 2001 3395

A98013857 2002 3500

A98013857 2003 1097

A99006090 2002 5500

A99006090 2003 3284

A99006090 2004 5341

A99006090 2010 10500

B01006402 2002 2625

B01006402 2004 1210

B02005510 2004 2625

B02005510 2008 1500

B02005510 2009 1500

B02005510 2010 1500

B03000809 2009 5500

B03000809 2010 3984

B04005526 2005 3500

B04005526 2009 2250

B04005526 2010 2250

B04005526 2011 5500

B05009832 2009 4500

B05009832 2010 4500

B06022503 2009 1259

B07001076 2008 4250

B08004772 2010 4000

B08015067 2010 4500

B08015067 2011 7500

B98007560 1999 2572.52

B98007560 2000 6360

B98007560 2001 1091.25

;;;;

   run;

proc sort nodupkey data=have(keep=year) out=year;

   by year;

   run;

data haveV / view=haveV;

   set year have;

   run;

proc transpose let prefix=Y data=haveV out=wide(where=(not missing(id)));

   by id;

   id year;

   var amt;

   run;

proc contents varnum data=wide;

   run;

proc print;

   run;

[bfolzblu]

Thank you, that did the trick beautifully! I have also resolved the issue in my data of non unique student to aid year.

[art297]

Alternatively, you can just reorder the output file with something like:

proc sql noprint;

  select name

    into : names

            separated by " "

        from dictionary.columns

          where libname="WORK" and

              memname="HAVE" and

              name like '^_%' escape '^'

            order by name

  ;

quit;

data want;

  retain id &names.;

  set have;

run;

[bfolzblu]

Thank you, I will see about trying that.

[hellind]

Another trick which I use is double tranpose.

1. Transpose the 'transpose output'

2. Then transpose again