Hi, I'm still a newbie with SAS an I need help with maybe a simple question.
How do you add a subtotal column in a database?
Say this is my sample dataset:
data have; input zip $ branch $ count; datalines; 501 52--- 1 501 81--- 2 501 99--- 1 101 22--- 2 101 23--- 48 101 31--- 40 101 42--- 56 ; run; proc print; run;
Which gives me this in the results window:
Table below shows what I want to happen with the database:
I tried to Google and I'm confused whether I should use tabulate or sql or what. (I also don't know SQL by the way.)
Any help would be greatly appreciated.
Regards,
Tony
Do like this
data have;
input zip $ branch $ count;
datalines;
501 52--- 1
501 81--- 2
501 99--- 1
101 22--- 2
101 23--- 48
101 31--- 40
101 42--- 56
;
run;
proc sql;
create table want as
select *
,sum(count) as Count_Subtotals
,count/ calculated Count_Subtotals as Percent_Count format=percent.
from have
group by zip;
quit;
Do like this
data have;
input zip $ branch $ count;
datalines;
501 52--- 1
501 81--- 2
501 99--- 1
101 22--- 2
101 23--- 48
101 31--- 40
101 42--- 56
;
run;
proc sql;
create table want as
select *
,sum(count) as Count_Subtotals
,count/ calculated Count_Subtotals as Percent_Count format=percent.
from have
group by zip;
quit;
Thanks for the quick response draycut!
The code worked flawlessly. I'm super happy to see that the code is simpler than I expected.
Curious question though. Just in case I have multiple 'count' columns in another database, can I just do this? (see code)
proc sql;
create table want as
select *
,sum(count) as Count_Subtotals
,count/ calculated Count_Subtotals as Percent_Count format=percent.
,sum(count_2) as Count_Subtotals_2
,count/ calculated Count_Subtotals_2 as Percent_Count_2 format=percent.
from have
group by zip;
quit;
Or do I need to repeat the proc sql blocks per column?
No problem, glad to help 🙂
If the count colums are in different data sets, you can not.
If they are, you can use the same code
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 25. 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.