Add a subtotal column in the database

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Add a subtotal column in the database

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:

 


sas subtotals 1.pngInput dbase

Table below shows what I want to happen with the database:

sas subtotals.pngOutput dbase

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


Accepted Solutions
Solution
‎01-20-2018 06:00 AM
PROC Star
Posts: 1,217

Re: Add a subtotal column in the database

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;

View solution in original post


All Replies
Solution
‎01-20-2018 06:00 AM
PROC Star
Posts: 1,217

Re: Add a subtotal column in the database

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;
Occasional Contributor
Posts: 9

Re: Add a subtotal column in the database

[ Edited ]

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?

PROC Star
Posts: 1,217

Re: Add a subtotal column in the database

No problem, glad to help Smiley Happy

 

If the count colums are in different data sets, you can not.

 

If they are, you can use the same code

Occasional Contributor
Posts: 9

Re: Add a subtotal column in the database

Yes, they're just 2 columns in the same database. Awesome. Thanks again draycut.
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 178 views
  • 2 likes
  • 2 in conversation