## sums and counts by group

Solved
Super Contributor
Posts: 269

# sums and counts by group

Beginning SAS programmer.

Given data like (region populated after a merge):

identiferpayoutregion
1101
2202
3101
4203
5101
6202
7103
8203
9102
10204
11104
12202

I want output like:

regioncount identifiersum payout
1330
2470
3350
4230

I know SQL and I can do this easily with Proc SQL:

PROC SQL;

SELECT region, COUNT(identifier), SUM(payout)

FROM my_data

GROUP BY region

ORDER BY region;

QUIT;

But I want to do it the proper SAS way and use Proc Means or Proc Tabulate or Proc something.

Can someone please give me the lesson?

Accepted Solutions
Solution
‎02-08-2012 01:00 PM
SAS Super FREQ
Posts: 9,435

## Re: sums and counts by group

And, in the interest of completeness, PROC REPORT and PROC TABULATE will also give you equivalent results. If you want a SAS dataset instead of just a report, then you can use the OUT= option with those two procedures, as well (and the OUTPUT statement with PROC MEANS/SUMMARY, as shown by Art's example).  The difference between Art's example, and these is that my procedures create reports in the HTML file. These procedures could create output datasets, as well.

cynthia

data mydata;

infile datalines;

input identifer payout region ;

return;

datalines;

1 10 1

2 20 2

3 10 1

4 20 3

5 10 1

6 20 2

7 10 3

8 20 3

9 10 2

10 20 4

11 10 4

12 20 2

;

run;

ods html file='c:\temp\mytables.html';

proc means data=mydata sum;

class region;

var payout;

run;

class region;

var payout;

tables region, payout*(N sum);

run;

proc report data=mydata nowd out=work.repout;

column region payout payout=paysum;

define payout / n 'Count Indicator';

define paysum / sum 'Payout Sum';

run;

ods html close;

All Replies
Super User
Posts: 8,220

## sums and counts by group

proc sql is proper sas.  However, to get proc summary to produce the same file you could use:

data my_data;

input identifer payout region;

cards;

1          10          1

2          20          2

3          10          1

4          20          3

5          10          1

6          20          2

7          10          3

8          20          3

9          10          2

10          20          4

11          10          4

12          20          2

;

proc summary data=my_data nway;

var payout;

class region;

output out=want (drop=_

n=count_identifier

sum=sum_payout;

run;

Solution
‎02-08-2012 01:00 PM
SAS Super FREQ
Posts: 9,435

## Re: sums and counts by group

And, in the interest of completeness, PROC REPORT and PROC TABULATE will also give you equivalent results. If you want a SAS dataset instead of just a report, then you can use the OUT= option with those two procedures, as well (and the OUTPUT statement with PROC MEANS/SUMMARY, as shown by Art's example).  The difference between Art's example, and these is that my procedures create reports in the HTML file. These procedures could create output datasets, as well.

cynthia

data mydata;

infile datalines;

input identifer payout region ;

return;

datalines;

1 10 1

2 20 2

3 10 1

4 20 3

5 10 1

6 20 2

7 10 3

8 20 3

9 10 2

10 20 4

11 10 4

12 20 2

;

run;

ods html file='c:\temp\mytables.html';

proc means data=mydata sum;

class region;

var payout;

run;

class region;

var payout;

tables region, payout*(N sum);

run;

proc report data=mydata nowd out=work.repout;

column region payout payout=paysum;

define payout / n 'Count Indicator';

define paysum / sum 'Payout Sum';

run;

ods html close;

Super Contributor
Posts: 1,636

## Re: sums and counts by group

Hi Art,

We came up with almost identicle code.

data have;

input identifer payout region;

cards;

1 10 1

2 20 2

3 10 1

4 20 3

5 10 1

6 20 2

7 10 3

8 20 3

9 10 2

10 20 4

11 10 4

12 20 2

;

proc summary nway;

class region;

var payout;

output out=want(drop=_ n=count_identifer sum=sum_payout;

run;

proc print data=want;run;

Obs    region    identifer    payout

1        1          3          30

2        2          4          70

3        3          3          50

4        4          2          30

Super User
Posts: 8,220

## Re: sums and counts by group

Linlin: be careful or you might end up becoming a psychologist

Contributor
Posts: 27

## Re: sums and counts by group

I am beginer to SAS and you just solved my problem. Thanks for this code.

New Contributor
Posts: 4

## Re: sums and counts by group

Use proc summary. The nway options gives only the region total, without you a grand total would be created as we

Super User
Posts: 8,220

## Re: sums and counts by group

Of course, if we're going for compleness, there is also the datastep approach:

proc sort data=my_data;

by region;

run;

data want (drop=identifer payout);

set my_data;

by region;

if first.region then do;

sum_payout=payout;

count_identifier=1;

end;

else do;

sum_payout+payout;

count_identifier+1;

end;

if last.region then output;

run;

proc print;

run;

SAS Super FREQ
Posts: 9,435

## sums and counts by group

True! I generally go there when REPORT, TABULATE and MEANS won't do what I want. ;-)

cynthia

Super Contributor
Posts: 383

## Re: sums and counts by group

THIS IS WHAT I WAS LOOKING FOR, DOING IT IN DATA STEP
Super Contributor
Posts: 269

## sums and counts by group

Okay, in the rough order received:.

art:

proc summary data=my_data nway;
var payout;
class region;
output out=want (drop=_
n=count_identifier
sum=sum_payout;
run;

This generated no output and was frustrating.  See below.

cynthia:

proc report data=mydata nowd out=work.repout;
column region payout payout=paysum;
define payout / n 'Count Indicator';
define paysum / sum 'Payout Sum';
run;

This introduced a whole new window to me.  Interesting. Helpful- introduces me to something new.

cynthia:

class region;
var payout;
tables region, payout*(N sum);
run;

This is what I had in mind and thought was the answer.  Full marks.  I used (N*F=COMMA12. sum*F=DOLLAR12.)

linlin:

art'a post plus
proc print data=want;
run;

art:

data want (drop=identifer payout);
set my_data;
by region;
if first.region then do;
sum_payout=payout;
count_identifier=1;
end;
else do;
sum_payout+payout;
count_identifier+1;
end;
if last.region then output;
run;
proc print;
run;

Oh man!  This is code that looks like something I could get behind and understand.  I haven't been able to make it work but I'm looking at it.  Helpful- introduces me to something new.

Thanks to all.

Contributor
Posts: 20

## Re: sums and counts by group

What would be the easiest way to do it if you have more fields?

I have station, ssn, invoice number, line number and amount

I want to end up with each unique station number with a count of ssn's, a count of their invoices, a count of line items, and a total cost?

I'm not having much luck with it

Super User
Posts: 10,860

## Re: sums and counts by group

List these variables:

proc summary data=my_data nway;

var payout;

class region;

output out=want (drop=_

n(ssn num)=ssn_x num_x

sum(ssn num)=ssn_sum num_sum;

run;

New Contributor
Posts: 3

## Re: sums and counts by group

Hi Marilyn,

proc means data = have nway noprint;

var payout ssn invoice_num line_num;

class region;

output out= want (drop= _

n(ssn invoice_num line_num) = ssn_count invoice_count line_count

sum(amount)  = cost;

run;

It will create only output dataset not the report (printed report). If you want report then remove the "noprint" option in proc means statement.

Hope this works fine!!

Thanks,

🔒 This topic is solved and locked.