topic Re: Add a subtotal column in the database in SAS Programming

Add a subtotal column in the database

Tony5 — Sat, 20 Jan 2018 08:07:06 GMT

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:

Input dbase

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

Output 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

Re: Add a subtotal column in the database

PeterClemmensen — Sat, 20 Jan 2018 09:22:20 GMT

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;

Re: Add a subtotal column in the database

Tony5 — Sat, 20 Jan 2018 11:17:09 GMT

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?

Re: Add a subtotal column in the database

PeterClemmensen — Sat, 20 Jan 2018 11:13:09 GMT

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

Re: Add a subtotal column in the database

Tony5 — Sat, 20 Jan 2018 11:15:56 GMT

Yes, they're just 2 columns in the same database. Awesome. Thanks again draycut.