PROC SQL, group by & total

[_LB]

Hello all,

Is there a easy way to get totals by sub-group and a total overall in the same column?

So that for example in the code below-

proc sql;

create table numbers as select A.MRSA_CLASS, put(sum(com)/count(*), percent10.) as compliance,put(sum(TEST)/count(*), percent10.) as TESTED,

from MRSA_ADM_ARR as a

group by  A.MRSA_CLASS;

quit;

I will get

CLASS Compliance Tested

A            75%           90%

B            55%           65%

...           ..                ..

Z             80%          90%

What I would like is the above but also a grand total at the bottom without necessarily doing the code twice, one with grouping, one without.

Thank you again for your time.

Best,

Lawrence

[art297]

One possibility would be to use the create table statement in sql, rather than printing the results, and then use proc print with a summary line (e.g., http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002262174.htm )

That would only require one pass thru the data, one pass thru the summaries, and very little extra coding.

[Reeza]

Depending on your data structure consider using proc means with the Class Statement.

It looks like your variables are indicators (ie 1, or 0) so the mean or average is the same as calculating a percentage.

proc means data= MRSA_ADM_ARR noprint;

class mrsa_class;

var com test;

output out=sample_data mean=/autoname;

run;

Your output will contain a _TYPE_ variable, the type 0 is your overall and Type1 is your individual summary.

[_LB]

To Art & Reeza-

Thanks for your helpful answers.

@Reeza-I am well acquainted with proc means but was looking for an answer in SQL, which I believe there is none that is suitable at the moment.

Proc Means it is!

Lawrence

[Hobbes · Accepted solution]

Is this what you're looking for:

proc sql;

      select a.*

      from sashelp.class as a

      OUTER UNION CORRESPONDING

      select '* TOTAL AGE *' as name, sum(age) as age

      from sashelp.class

      group by 1;

quit;

[_LB]

Hobbes-

I had to modify the query a tiny bit for the outcome but it works! Thank you!

Lawrence

[art297]

Just FWIW, while the solution Hobbes proposed will get you the desired result, it involves two passes through your data.  If your file isn't very large, that definitely requires the least coding (though not by much).  If your dataset is fairly large, though, I would still recommend a two-step approach (like combining proc sql with proc print) as the proc print portion would only need to read through the much smaller summarized file.

[_LB]

Art,

In the case, the data set isn't that big but duly noted on larger data sets in the future.

Thank you.

Lawrence