transpose and sort columns

Reply
New Contributor
Posts: 2

transpose and sort columns

 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.

 

RankMAR18FEB18JAN18DEC17NOV17OCT17
145.00%96.00%61.00%21.00%  
298.00%68.00%58.00%32.00%  
376.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.

PROC Star
Posts: 1,765

Re: transpose and sort columns

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;
Super User
Posts: 10,761

Re: transpose and sort columns

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;
New Contributor
Posts: 2

Re: transpose and sort columns

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?
Super User
Posts: 13,498

Re: transpose and sort columns

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

 

Super User
Posts: 10,761

Re: transpose and sort columns

@ballardw 

I think should use better.

 

   class rank;
   class month/order=internal descending;
Super User
Posts: 13,498

Re: transpose and sort columns


@Ksharp wrote:

@ballardw 

I think should use better.

 

   class rank;
   class month/order=internal descending;

Agreed.

 

Occasional Contributor
Posts: 8

Re: transpose and sort columns

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;
Ask a Question
Discussion stats
  • 7 replies
  • 129 views
  • 0 likes
  • 5 in conversation