topic PROC SQL, group by & total in SAS Programming

PROC SQL, group by & total

_LB — Wed, 07 Sep 2011 22:08:58 GMT

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

PROC SQL, group by & total

art297 — Wed, 07 Sep 2011 22:17:18 GMT

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.

PROC SQL, group by & total

Reeza — Wed, 07 Sep 2011 22:45:40 GMT

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.

PROC SQL, group by & total

_LB — Wed, 07 Sep 2011 22:49:56 GMT

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

PROC SQL, group by & total

Hobbes — Thu, 08 Sep 2011 09:58:11 GMT

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;

PROC SQL, group by & total

_LB — Thu, 08 Sep 2011 15:49:16 GMT

Hobbes-

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

Lawrence

PROC SQL, group by & total

art297 — Thu, 08 Sep 2011 16:05:12 GMT

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.

PROC SQL, group by & total

_LB — Thu, 08 Sep 2011 16:09:11 GMT

Art,

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

Thank you.

Lawrence