SAS proc tabulate outputs column total

Question

I have two indexes for serveral cities and I want to output a summary.

Here's my code

proc tabulate data=HAVE missing;
class Date City/order=data preloadfmt;
format City $Areaformat.;
var Index1 Index2;
table (Date=''),
      (Index1={Label="AAA" s=[cellwidth=2in just=c]}*(sum='') Index2={Label="BBB" s=[cellwidth=2in 
      just=c]}*(sum='')) * (City='' all=Total);
run;

Here's the sample output.

                                  Index1                            Index2
                   City1    City2    City3    Total    City1     City2    City3    Total
01Nov2014            1        2        1        4        1         1        1        3  
02Nov2014            2        2        1        5        2         1        2        5

But I need something like

                                  Index1                            Index2                   Index Total
                   City1    City2    City3    Total    City1     City2    City3    Total     Grand Total
01Nov2014            1        2        1        4        1         1        1        3          7
02Nov2014            2        2        1        5        2         1        2        5          10

Also, I have another output from proc tabulate which shows the ideal index on each day.

proc tabulate data=Ideal missing;
class Date;
var Index1 Index2;
table (Date=''), 
      (Index1={Label="Ideal Index1"}*(sum='Total') Win={Index2="Ideal Index2"}*(sum='Total'));
run;

Here's another output.

                  Ideal1        Ideal2
                   Total         Total
01Nov2014            4            3  
02Nov2014            5            5

Is there a way to append the ideal indexes table to the right of desired output above (easier for comparison rather than seperate them into two output tables)? Finally to be like this

                      Index1                      Index2           Index Total  Ideal1  Ideal2  Ideal Total  Diff           
           City1  City2  City3  Total  City1  City2  City3  Total  Grand Total   Total  Total    Total     
01Nov2014   1      2      1       4      1      1      1      3         7          4      3       7            0
02Nov2014   2      2      1       5      2      1      2      5         10         5      5       10           0

Reeza Reeza · Accepted Answer · 2014-11-20T22:09:24

Use dtdate9 to format your datetime to date9. You may want multiple class statements since that won't be a preloadformat. Add another all at the end for the Grand Total.

proc tabulate data=HAVE missing;
class City/order=data preloadfmt;
class Date/order=data;
format City $Areaformat.;
format date dtdate9.;
var Index1 Index2;
table (Date=''),
      (Index1={Label="AAA" s=[cellwidth=2in just=c]}*(sum='') Index2={Label="BBB" s=[cellwidth=2in 
      just=c]}*(sum='')) * (City='' all=Total) all='Grand Total';
run;

SAS proc tabulate outputs column total

1 Answers