DATA Step, Macro, Functions and more

PROC SQL, group by & total

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 170
Accepted Solution

PROC SQL, group by & total

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


Accepted Solutions
Solution
‎09-08-2011 05:58 AM
Occasional Contributor
Posts: 16

PROC SQL, group by & total

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;

View solution in original post


All Replies
PROC Star
Posts: 7,468

PROC SQL, group by & total

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.

Super User
Posts: 19,772

PROC SQL, group by & total

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.

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL, group by & total

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

Solution
‎09-08-2011 05:58 AM
Occasional Contributor
Posts: 16

PROC SQL, group by & total

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;

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL, group by & total

Hobbes-

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

Lawrence

PROC Star
Posts: 7,468

PROC SQL, group by & total

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.

Regular Contributor
Regular Contributor
Posts: 170

PROC SQL, group by & total

Art,

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

Thank you.

Lawrence

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1007 views
  • 0 likes
  • 4 in conversation