topic Re: sums and counts by group in SAS Procedures

sums and counts by group

HB — Wed, 08 Feb 2012 17:24:57 GMT

Beginning SAS programmer.

Given data like (region populated after a merge):

identifer	payout	region
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

I want output like:

region	count identifier	sum payout
1	3	30
2	4	70
3	3	50
4	2	30

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?

sums and counts by group

art297 — Wed, 08 Feb 2012 17:52:15 GMT

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;

Re: sums and counts by group

Cynthia_sas — Wed, 08 Feb 2012 18:00:49 GMT

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;

proc tabulate data=mydata f=6. out=work.tabout;

class region;

var payout;

tables region, payout*(N sum);

run;

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

column region payout payout=paysum;

define region / group style(column)=Header;

define payout / n 'Count Indicator';

define paysum / sum 'Payout Sum';

run;

ods html close;

Re: sums and counts by group

terryfearn — Wed, 08 Feb 2012 18:03:12 GMT

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

Re: sums and counts by group

Linlin — Wed, 08 Feb 2012 18:08:09 GMT

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

Re: sums and counts by group

art297 — Wed, 08 Feb 2012 18:11:58 GMT

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

Re: sums and counts by group

art297 — Wed, 08 Feb 2012 18:21:26 GMT

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;

sums and counts by group

Cynthia_sas — Wed, 08 Feb 2012 18:57:02 GMT

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

cynthia

sums and counts by group

HB — Wed, 08 Feb 2012 21:08:02 GMT

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 region / group style(column)=Header;
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:

proc tabulate data=mydata f=6. out=work.tabout;
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;

Aha! Output! I told you I was beginner. Helpful. Makes Art's answer helpful.

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.

Re: sums and counts by group

Marilyn — Wed, 23 Apr 2014 21:23:56 GMT

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

Re: sums and counts by group

Ksharp — Thu, 24 Apr 2014 02:15:17 GMT

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;

Re: sums and counts by group

deval — Sat, 25 Oct 2014 10:49:48 GMT

Hi Marilyn,

This code may helps to answer your question.

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,

Re: sums and counts by group

pandyat0 — Fri, 21 Apr 2017 02:41:49 GMT

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

Re: sums and counts by group

Cruise — Sat, 22 Apr 2017 14:18:29 GMT

THIS IS WHAT I WAS LOOKING FOR, DOING IT IN DATA STEP