CODEWITHSUNDEEP
sas
Kavitha
Kavitha

Reputation: 371

PROC TABULATE WITH TOTAL

I am doing reports with proc tabulate, but unable to add total in a report.

Example

+--------+------+----------+--------+---+---+---+
|  Shop  | Year |  Month   | Family | A | B | C |
+--------+------+----------+--------+---+---+---+
| raoas  | 2006 | january  | TA12   | 5 | 6 | 0 |
| taba   | 2008 | january  | TS01   | 0 | 1 | 1 |
| suptop | 2008 | april    | TZ05   | 0 | 0 | 1 |
| taba   | 2006 | December | TA12   | 5 | 6 | 0 |
| raoas  | 2008 | january  | TA15   | 0 | 2 | 0 |
| sup    | 2008 | april    | TQ05   | 0 | 1 | 1 |
+--------+------+----------+--------+---+---+---+

code

proc tabulate data=REPORTDATA_T6 format=12.;
            CLASS YEAR;
            var A  C;
            table  (A  C)*SUM='',YEAR=''
            /box = 'YEAR';
        TITLE 'FORECAST SUMMARY';
  run;

output 

YEAR    2006 2008 2009
A       800 766 813
C       854 832 812

I tried with... table(A C)*sum,year all... it will sum up for all the years but I want by year. I tried with all the possible ways and tried... table(A C)*sum all,year. It will give number of observations ie N.. Thanx JON CLEMENTS But I dont want to add as TOTAL VARIABLE in the table, becoz this is a sample data but the number of variables are more then 10, some time I need to change variables, So, every time i dont want to add new variable as total.

Upvotes: 0

Views: 2304

Answers (1)

Dmitry Shopin
Dmitry Shopin

Reputation: 1763

I'm not sure if it's possible to do what you want in one step using only original data. Keyword ALL works only for summing up categories of CLASS-variables, but you want to sum up two different variables.

But it's easy enough with interim step, creating dataset where A, B, C variables will become categories of one variable:

data REPORTDATA_T6;
    input Shop $ Year Month $ Family $ A B C;
datalines;
raoas 2006 january TA12 5 6 0
taba 2008 january TS01 0 1 1
suptop 2008 april TZ05  0 0  1
taba 2006 December TA12 5 6 0
raoas 2008 january TA15 0 2 0
sup 2008 april TQ05 0 1 1
;
run;
proc sort data=REPORTDATA_T6; by Shop Year Month Family; run;

proc transpose data=REPORTDATA_T6 out=REPORTDATA_T6_long;
    var A B C;
    by Shop Year Month Family;
run;

proc tabulate data=REPORTDATA_T6_long;
    class _NAME_  YEAR;
    var COL1;
    table  (_NAME_ all)*COL1=' '*SUM=' ', YEAR=' '
    /box = 'YEAR';
    TITLE 'FORECAST SUMMARY';
run;

Upvotes: 1

Related Questions