tabulate not printing the table output

[wfc]

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 

[SASJedi]

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

[wfc]

i want the output exactly as i showed in the above . The solution will not work for me.

[Reeza]

There isn't a procedure that automatically does that.

To get the end results, pivot and sum in a data step or you can pivot the proc tabulate.

Note that there is no two level headings as in the picture so you only get a single column heading.

[wfc]

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?

[Tom]

---

@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?

[Tom]

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.

[wfc]

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;

[Tom]

---

@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.

[Reeza]

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;

[Ksharp · Accepted solution]

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;