i want the ouput to have the first table which is pivot.. but after i run its printing the output as the second table but not the pivot which i am expecting
You could try PROC REPORT.
data have;
input category $ month $ value;
datalines;
a jan 100
a feb 150
b jan 130
b feb 300
c jan 11
c feb 23
c mar 26
;
proc report data=have nowd out=want;
column category month,value all;
define category/group;
define month/across '';
define value/analysis '';
define all/computed;
compute all;
all=sum(_c2_,_c3_,_c4_);
endcomp;
run;
The output table has the data you asked for, just not in the format you want. You need to transpose the data:
proc sort data=test;
by month;
run;
proc transpose data=test out=test1(drop=_:);
by month;
id category;
var value_sum;
run;
proc print; run;
Obs | month | a | b | c |
---|---|---|---|---|
1 | 250 | 430 | 57 | |
2 | feb | 150 | 300 | 23 |
3 | jan | 100 | 130 | 11 |
4 | mar | . | . | 23 |
i want the output exactly as i showed in the above . The solution will not work for me.
can you please guide me on how to do that, i am a newbie at SAS. i dont require 2 level heading, even one level which shows the month should work for me. can you please provide the syntax on acheving the same?
@wfc wrote:
i want the output exactly as i showed in the above . The solution will not work for me.
What does that mean? A dataset and a report are two totally different things. A dataset cannot have variable names that look like:
Perhaps you should just use ODS EXCEL to route your report to a spreadsheet?
For more help share your program as text, not a photograph.
Use the Insert SAS Code button to get a pop-up window where you can paste/edit the SAS code so that the forum does not treat it as paragraphs.
data have; input category $ month $ value; datalines; a jan 100 a feb 150 b jan 130 b feb 300 c jan 11 c feb 23 c mar 26 ; ods output table=tabulate_results; proc tabulate data=have ; class category month; var value; Table category, (month all='Total')*(value='')*(sum=''); quit; proc print data=tabulate_results; run;
@wfc wrote:
data have; input category $ month $ value; datalines; a jan 100 a feb 150 b jan 130 b feb 300 c jan 11 c feb 23 c mar 26 ; ods output table=tabulate_results; proc tabulate data=have ; class category month; var value; Table category, (month all='Total')*(value='')*(sum=''); quit; proc print data=tabulate_results; run;
See what happens when you give the forum code as if it was your comments?
Let's reformat that so it is readable.
data have;
input category $ month $ value;
datalines;
a jan 100
a feb 150
b jan 130
b feb 300
c jan 11
c feb 23
c mar 26
;
Since your two class variables are character you can simply use something like this to get the SUM , TRANSPOSE and then add the TOTAL variable.
proc summary data=have nway;
class category month ;
var value;
output out=summary sum=;
run;
proc transpose data=summary out=wide(drop=_name_) ;
by category ;
id month;
var value;
run;
data want;
set wide;
total=sum(of _numeric_);
run;
Result
Obs category feb jan mar total 1 a 150 100 . 250 2 b 300 130 . 430 3 c 23 11 26 60
If CATGORY was numeric you would have to account for that in the step that makes the TOTAL.
total=sum(of _numeric_, -category);
If MONTH was numeric you might need to add the PREFIX= option to the PROC TABULATE statement so that the generated variable names will start with a letter.
Very slight modification on @Tom correct solution.
Use a prefix for the month when you transpose and the IDLABEL option to get the correct labels. This way when summing the variables you can refer to them with the known prefix to avoid any other variables getting included.
proc transpose data=summary out=wide(drop=_name_) prefix=month_;
by category ;
id month;
idlabel month;
var value;
run;
data want;
set wide;
total=sum(of month_:);
run;
proc print data=want label noobs;run;
You could try PROC REPORT.
data have;
input category $ month $ value;
datalines;
a jan 100
a feb 150
b jan 130
b feb 300
c jan 11
c feb 23
c mar 26
;
proc report data=have nowd out=want;
column category month,value all;
define category/group;
define month/across '';
define value/analysis '';
define all/computed;
compute all;
all=sum(_c2_,_c3_,_c4_);
endcomp;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.