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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Hobbes
Calcite | Level 5

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

7 REPLIES 7
art297
Opal | Level 21

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

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
Fluorite | Level 6 _LB
Fluorite | Level 6

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

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
Fluorite | Level 6 _LB
Fluorite | Level 6

Hobbes-

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

Lawrence

art297
Opal | Level 21

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
Fluorite | Level 6 _LB
Fluorite | Level 6

Art,

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

Thank you.

Lawrence

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4413 views
  • 0 likes
  • 4 in conversation