topic Re: transpose and sort columns in SAS Programming

transpose and sort columns

Cbob03 — Tue, 01 May 2018 02:36:32 GMT

Hello,

I have the below data example.

Data aa1;
input rank score month $5.;
infile cards;
cards;
1 0.45 MAR18
2 0.98 MAR18
3 0.76 MAR18
1 0.96 FEB18
2 0.68 FEB18
3 0.55 FEB18
1 0.61 JAN18
2 0.58 JAN18
3 0.45 JAN18

4 0.73 JAN18

5 0.40 JAN18
1 0.21 DEC17
2 0.32 DEC17
3 0.49 DEC17

4 0.86 DEC17
;
Run;

I want the output to look as below.

Rank	MAR18	FEB18	JAN18	DEC17	NOV17	OCT17
1	45.00%	96.00%	61.00%	21.00%
2	98.00%	68.00%	58.00%	32.00%
3	76.00%	55.00%	45.00%	49.00%
4			73.00%	86.00%
5			40.00%

Basically I need help with transposing the raw data as above(appreciate if you could show me a method other than proc transpose). I tried using proc transpose but where I struggle was arranging/sorting the column names by descending yyyymm. My data goes all the way back to Feb17 and every month I will be adding a new month. For instance, in May18 my month column range will be May18-Feb17. Any help is much appreciated!

Thanks.

Re: transpose and sort columns

novinosrin — Tue, 01 May 2018 03:56:49 GMT

Data aa1;
input rank score month $5.;
infile cards;
cards;
1 0.45 MAR18
2 0.98 MAR18
3 0.76 MAR18
1 0.96 FEB18
2 0.68 FEB18
3 0.55 FEB18
1 0.61 JAN18
2 0.58 JAN18
3 0.45 JAN18
4 0.73 JAN18
5 0.40 JAN18
1 0.21 DEC17
2 0.32 DEC17
3 0.49 DEC17
4 0.86 DEC17
;
Run;
proc sort;
by rank;
run;

proc transpose data=aa1 out=_aa1;
by rank;
var score;
id month;
run;

Re: transpose and sort columns

Ksharp — Tue, 01 May 2018 08:32:06 GMT

Data aa1;
input rank score month monyy5.;
format month monyy5.;
infile cards;
cards;
1 0.45 MAR18
2 0.98 MAR18
3 0.76 MAR18
1 0.96 FEB18
2 0.68 FEB18
3 0.55 FEB18
1 0.61 JAN18
2 0.58 JAN18
3 0.45 JAN18
4 0.73 JAN18
5 0.40 JAN18
1 0.21 DEC17
2 0.32 DEC17
3 0.49 DEC17
4 0.86 DEC17
;
Run;
proc tabulate data=aa1;
class rank month;
var score;
table rank,month*score=''*sum=''*f=percent8.2;
run;

Re: transpose and sort columns

Cbob03 — Tue, 01 May 2018 14:00:26 GMT

Thank Ksharp for your response. Your solution works but it doesn't display the months in descending order such as Mar18, Feb18, Jan18, Dec17... is there a way to sort the months in the tabulate proc?

Re: transpose and sort columns

ballardw — Tue, 01 May 2018 15:20:30 GMT

Data work.aa1;
input rank score month $5.;
datemonth= input(cats('01',month),date7.);
format datemonth monyy5.;
infile cards;
cards;
1 0.45 MAR18
2 0.98 MAR18
3 0.76 MAR18
1 0.96 FEB18
2 0.68 FEB18
3 0.55 FEB18
1 0.61 JAN18
2 0.58 JAN18
3 0.45 JAN18

4 0.73 JAN18

5 0.40 JAN18
1 0.21 DEC17
2 0.32 DEC17
3 0.49 DEC17

4 0.86 DEC17
;
Run;
proc sort data=work.aa1;
  by rank descending datemonth;
run;
proc tabulate data=work.aa1;
   class rank;
   class datemonth/order=data;
   var score;
   table rank=' '*score=' '*max=' '*f=percent8.2,
         datemonth=' '
         / box='Rank' row=float misstext=' '
   ;
run;

Since your example data doesn't have any "percent" in the decimal range do you actually want to display all those .00% values? If not remove the .2 in the percent8.2 format

Re: transpose and sort columns

JChambo — Wed, 02 May 2018 05:46:05 GMT

To reorder variables I have generally specified the order in a LENGTH or RETAIN statement before the SET statement. You can't do his in the proc transpose itself, but you can in a follow up data step.

Using proc sql's SELECT INTO you can create a macro variable that lists the months in MONYY format in date order, and use that macro variable in a LENGTH or RETAIN statement to set the variable order.

proc sql;
select distinct
	input(month,monyy.) as month_var_name format=monyy.
into
	: month_vars separated by ' '
from
	original_data
order by
	month_var_name
;quit;

data transposed_reordered;
length Rank $5 &month_vars 8;
set transposed;
run;

Re: transpose and sort columns

Ksharp — Wed, 02 May 2018 12:28:46 GMT

@ballardw

I think should use better.

   class rank;
   class month/order=internal descending;

Re: transpose and sort columns

ballardw — Wed, 02 May 2018 16:28:19 GMT

@Ksharp wrote:

@ballardw

I think should use better.
   class rank;
   class month/order=internal descending;

Agreed.