BookmarkSubscribeRSS Feed
Cbob03
Fluorite | Level 6

 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.

7 REPLIES 7
novinosrin
Tourmaline | Level 20
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;
Ksharp
Super User
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;
Cbob03
Fluorite | Level 6
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?
ballardw
Super User
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

 

Ksharp
Super User

@ballardw 

I think should use better.

 

   class rank;
   class month/order=internal descending;
ballardw
Super User

@Ksharp wrote:

@ballardw 

I think should use better.

 

   class rank;
   class month/order=internal descending;

Agreed.

 

JChambo
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1671 views
  • 0 likes
  • 5 in conversation