Grand Total

[RAF]

I have the following procedure that summarizes hours by district. How can add a grand total for all districts to this statement? Or do I have to use the proc tabulate?

proc sql;
select dcode, dname, sum(hrs) as hours
from hourstable
group by dcode, dname;

Thanks!

RAF

[art297]

I can't test it at the moment you could get it a number of ways other than proc tabulate (although that would work to).  Depends on what you want your output to look like.

One simple way would be to just expand your statement.  e.g.:

proc sql;

select dcode, dname, sum(hrs) as hours

from hourstable

group by dcode, dname;

select sum(hrs) as totalhours

from hourstable;

quit;

Conversely, you could add

create sometablename as

right after your proc sql call and then use proc print to print the file and sum the hours.  Using that method, would probably require the least coding yet still give you a nice looking output.

[RAF]

Thanks for the response.

I'm not extremely worried about the 'looks' of it. Just a Grand Total at the bottom, after all the district totals.

So far, the only I found is to use the more elaborate proc tabulate (table, etc...).

I was hoping something simple added to my proc sql

[SASKiwi]

Another way would be to create a dataset from the SQL then PROC PRINT it and use the SUM statement to give you a grand total at the bottom. 

[Cynthia_sas · Accepted solution]

Hi:

  If you use the PROC SQL and PROC PRINT method, then you will end up making 2 passes through the data -- one to do the summarize and create HOURS and the other to do the PROC PRINT and the SUM. Either TABULATE or REPORT would do the same report for you with one pass through the data. The cosmetic differences between the two reports are shown in the attached screenshot -- but with a bit of code, you could "fix" the cosmetics..

cynthia

data hourstable;

  infile datalines;

  input dcode $ dname $ hrs;

return;

datalines;

AAA Alan 10

AAA Alan  5

AAA Bob  10

AAA Bob  20

AAA Carl  5

AAA Carl  5

AAA Carl  5

BBB Dana  6

BBB Dana  4

BBB Dana  5

BBB Edie  5

BBB Edie 20

CCC Alan 10

CCC Alan  2

CCC Bob   5

CCC Bob   2

CCC Bob   3

CCC Dana  6

CCC Dana  4

CCC Dana  5

CCC Edie  5

CCC Edie  5

;

run;

ods html file='c:\temp\sum_tab_rep.html' style=sasweb;

proc tabulate data=hourstable f=comma6.;

  title '1) PROC TABULATE';

  class dcode dname;

  var hrs;

  table dcode*dname all='Total',

        hrs='Hours'*sum=' ';

run;

proc report data=hourstable nowd;

  title '2) PROC REPORT';

  column dcode dname hrs;

  define dcode / group;

  define dname / group;

  define hrs / sum 'Hours';

  rbreak after /summarize;

  compute after;

    dcode = 'Total';

  endcomp;

run;

ods _all_ close;

[art297]

Cynthia,

I obviously can't question the number of passes required.  But, I expanded your example dataset to have 2,200,000 records.

On my computer the proc sql version took 3 seconds, while your proposed methods took approximately 1.5 seconds each.

If the OP's data set is even that large, and since they had already written the initial proc sql code, I think it would take longer than 1.5 seconds to write the additional code.

However, you make a good point nonetheless!

[Cynthia_sas]

Art:

  Ah, but, learn to fish (write the code) and after the initial investment -- amortized over the life of the report usage -- I'm betting that will be a pretty good ROI. When you get into the world of adding subtotals and/or grand totals to data, for purposes of reporting, I just find it more straightforward to go directly to the two reporting workhorse procedures -- TABULATE and REPORT. TABULATE is the "Ginsu Steak Knife" of SAS -- it slices and dices data and adds summary lines where you want. REPORT has the RBREAK, BREAK statements and COMPUTE block and the ability to customize break lines and compute variables/report items in the procedure itself. And besides, I like to brag on the SAS procedures that are outside the SQL horizon.

cynthia

[Ksharp]

How about:

data temp;
input id     upc_code      year ;
cards;
2000351     1230070413     2004
2000351     .     2004
2000351     1230000018     2004
2000351     1230070499     2004
2000351     .     2004
2000351     .     2005
2000351     1230011813     2005
2000351     .     2005
2000351     .     2005
2000351     .     2005
2000351     1230070413     2005
2000351     .     2005
2000351     .     2005
2000351     .     2005
2000351     .     2005
2000351     1230011813     2005
;
run;

proc sql ;
  select id,year,sum(year) as sum
   from temp
     group by id,year
  union 
  select .,.,sum(year)as sum
   from temp
    order by sum ;
quit;

Ksharp