topic Creating a table of sums by group in SAS Programming

Creating a table of sums by group

AnEmu — Wed, 05 Dec 2012 09:30:11 GMT

Hello,

I have some data similar to what I've put below (only with many more variables and millions of records). I need some code to create a sum of the amount by region. I am aware of the proc print command that does this but I don't want to see a list of the observations for each region as there will be far too many (due to the millions of records). Ideally a new table with this information would be created or some way that I could reference these sums directly for a following piece of code.

If I haven't made myself clear enough and more clarification is needed then feel free to ask.

Thanks

data proctest ;

input age region $ amount ;

datalines ;

23 NE 364364

45 NW 483933

67 SE 383932

43 NE 483022

54 L 666602

22 SW 483392

77 E 111111

34 SE 292093

;

run;

Re: Creating a table of sums by group

RichardinOz — Wed, 05 Dec 2012 11:00:27 GMT

Proc means (or proc summary) will provide the data you want provided you specify sum, and use the statement Class region.

Another approach is to use SQL:

Proc sql ;

create table totals as

select distinct region

, sum(amount) as total

from proctest

group by region

;

Quit ;

Richard in Oz

Re: Creating a table of sums by group

ballardw — Wed, 05 Dec 2012 16:14:00 GMT

RichardinOz's hint of proc means or summary is probably easiest to code if you want to summarize many variables as it allows the use of variable lists and such.

proc means data=have noprint;

class region;

var amount1 - amount100; /* or such as _numeric_ as long your class variable isn't numeric, or v: for all variables starting with v */

output out=want sum=;

run;

There will be a variable _type_ that has levels of the class variable with the value of 0 indicating the overall total sum as well.