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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.