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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.