Help using Base SAS procedures

sums and counts by group

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 89
Accepted Solution

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: 8,743

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;

 

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;

View solution in original post


All Replies
PROC Star
Posts: 7,363

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=_Smiley Happy

           n=count_identifier

           sum=sum_payout;

run;

Solution
‎02-08-2012 01:00 PM
SAS Super FREQ
Posts: 8,743

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;

 

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;

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=_Smiley Happy 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

PROC Star
Posts: 7,363

Re: sums and counts by group

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

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

Attachment
PROC Star
Posts: 7,363

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: 8,743

sums and counts by group

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

cynthia

Frequent Contributor
Posts: 125

Re: sums and counts by group

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

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=_Smiley Happy
           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. 

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 Smiley Sad

Super User
Posts: 9,681

Re: sums and counts by group

List these variables:

proc summary data=my_data nway;

  var payout;

  class region;

  output out=want (drop=_Smiley Happy

           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,

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= _Smiley Happy

                 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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 98475 views
  • 18 likes
  • 10 in conversation