SAS Pivot table

[wfc]

data have;
input ID $ a_cnt $ b_cnt $ month $ L_1 $ L_2 $ Dum;
datalines;
1 2 1 jan A1 b1 0
2 0 0 feb a2 b2 0
3 1 0 mar a1 b2 0
4 0 1 jan a2 b3 0
;

proc print data=have;


proc tabulate data=have;
class l_1 l_2 month;
var a_cnt;
table l_1*l_2, (month all='Total')*(a_cnt='')*(sum='');
run;

i am trying to use tabulate to produce a table like below... but i am not able to acheive the same. any help much appreciated.

[ballardw]

First fix errors in your code/date. You are attempting to use a variable A_cnt as a VAR variable in Proc Tabulate. All Var variables, which are those that use statistics like SUM, STDDEV and such must be numeric.

Why do you make A_cnt and B_cnt character values?

Also you show values in variable L_1 of "A1" and "a1". Those will be different values in SAS and create different "totals" if you are using other more complex data later. Your desired output shows all the values of L_1 and L_2 in upper case. So with the example data shown there is not going to be an "easy" way to do such. You should make sure the values are as expected before sending them to Proc Tabulate.

Also, what is the variable Dum supposed to represent???

Your bottom "summary" rows are introducing the variable a sum in a column and row both. Tabulate doesn't do such the way it appears you are thinking.

You some things that are not easily done in tabulate such as what appear to be section headers like "Sum of A_cnt" and offset row/column headers. So you may have to discuss which is critical to appearance as you may have to move to a different procedure and quite possibly summarize the data before creating a report.

Suggestion: any time you have things that resemble dates try to provide a date value. SAS knows how to order those.

Random collections of letters like "jan" "feb" "mar" mean nothing and you will have to provide all the additional work to get them to display in order.