DATA Step, Macro, Functions and more

Proc transpose output guidance

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Proc transpose output guidance

[ Edited ]

Hi there,

For your kind information, I am trying to create a report where the mean, median and P90 will be presented for each month as follows:  

 

year                 jan                                      feb                                  mar                         apr              

                 mean median p90         mean median p90    mean median p90     mean median p90    

2011          2           3         7             3           4        8        4           5        7            3        5       7       

 

My current table as follows:

data have;
input year month $ mean median p90 ;
cards;
2011 jan 2 3 7
2011 feb 3 4 8
2011 mar 4 5 7
2011 apr 3 5 7
2011 may 3 6 8
2012 jan 3 5 7
2012 feb 3 6 8
2012 apr 3 5 7
2012 may 3 6 8
;
run;

Thank you in advance for your kind help.

Regards,

 

Swain

Accepted Solutions
Solution
‎03-02-2017 02:38 PM
Respected Advisor
Posts: 4,937

Re: Proc transpose output guidance

Posted in reply to DeepakSwain

proc tabulate will do the job, with some help to get the month order correct:

 

proc format;
value m
1 = "January"
2 = "February"
3 = "March"
4 = "April"
5 = "May";
run;

data have;
input year month $ mean median p90 ;
m = whichc(month, "jan", "feb", "mar", "apr", "may");
format m m.;
drop month;
cards;
2011 jan 2 3 7
2011 feb 3 4 8
2011 mar 4 5 7
2011 apr 3 5 7
2011 may 3 6 8
2012 jan 3 5 7
2012 feb 3 6 8
2012 apr 3 5 7
2012 may 3 6 8
;

proc tabulate data=have format=best6.;
class year m;
var mean median p90;
table year="Year", m=''*(mean median p90)*'mean'='';
run;
PG

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Proc transpose output guidance

Posted in reply to DeepakSwain

Is your actual data presummarized or raw?

 

with raw data:

proc tabulate data=raw;
  class year month;
  var value;
  table year,
        month*value=''*(mean median P90);
run;

should work

 

Frequent Contributor
Posts: 104

Re: Proc transpose output guidance

Hi Ballardw,

 

Summary has already been created and I am stuck at last step. If I try to put the code provided by you, what I will put in place of "value".

Thank you in advance for your kind reply. 

Regards,

 

Swain
Frequent Contributor
Posts: 104

Re: Proc transpose output guidance

[ Edited ]

Hi there,

 

Thank you for providing me the solution using proc tabulate. This procedure was new to me. Now I got my answer which I asked you yesterday. 

 

Thank you once again.

 

Regards,

 

Swain
Solution
‎03-02-2017 02:38 PM
Respected Advisor
Posts: 4,937

Re: Proc transpose output guidance

Posted in reply to DeepakSwain

proc tabulate will do the job, with some help to get the month order correct:

 

proc format;
value m
1 = "January"
2 = "February"
3 = "March"
4 = "April"
5 = "May";
run;

data have;
input year month $ mean median p90 ;
m = whichc(month, "jan", "feb", "mar", "apr", "may");
format m m.;
drop month;
cards;
2011 jan 2 3 7
2011 feb 3 4 8
2011 mar 4 5 7
2011 apr 3 5 7
2011 may 3 6 8
2012 jan 3 5 7
2012 feb 3 6 8
2012 apr 3 5 7
2012 may 3 6 8
;

proc tabulate data=have format=best6.;
class year m;
var mean median p90;
table year="Year", m=''*(mean median p90)*'mean'='';
run;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 123 views
  • 2 likes
  • 3 in conversation