Creating a table of sums by group

Reply
Occasional Contributor
Posts: 5

Creating a table of sums by group

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;

Super Contributor
Posts: 644

Re: Creating a table of sums by group

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

Super User
Posts: 10,516

Re: Creating a table of sums by group

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.

Ask a Question
Discussion stats
  • 2 replies
  • 172 views
  • 0 likes
  • 3 in conversation