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
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;
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.
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.
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
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;
Hobbes-
I had to modify the query a tiny bit for the outcome but it works! Thank you!
Lawrence
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.
Art,
In the case, the data set isn't that big but duly noted on larger data sets in the future.
Thank you.
Lawrence
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: