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.
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;
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;
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.