BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RAF
Calcite | Level 5 RAF
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;


tab_report_grand_total.jpg

View solution in original post

7 REPLIES 7
art297
Opal | Level 21

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
Calcite | Level 5 RAF
Calcite | Level 5

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
PROC Star

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
SAS Super FREQ

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;


tab_report_grand_total.jpg
art297
Opal | Level 21

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
SAS Super FREQ

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 6918 views
  • 3 likes
  • 5 in conversation