freq and sum and average per category

Frequent Contributor
Posts: 107

freq and sum and average per category

Hello

I want to ask please how to create the desired output with one proc statement.

Required Output:
Cat    Freq      Sum_Wellness      Avg_Wellness
A       7            440                          62.8
B       3            110                          36.6
All     10            550                         55.0

Raw data :

Data aaa;
Input lakID Cat \$ Wellness;
Cards;
1 A 10
2 A 30
3 B 20
4 A 60
5 B 70
6 B 20
7 A 50
8 A 100
9 A 40
10 A 150
;
Run;

Super User
Posts: 9,941

Re: freq and sum and average per category

proc summary is all you need.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 373

Re: freq and sum and average per category

Use proc summary and set output sum=sum_wellness and mean=avg_wellness

//Fredrik

Frequent Contributor
Posts: 107

Re: freq and sum and average per category

Thanks.

2 problems:

1- I want to define format

Format  _FREQ_  COMMA12.  sum_wellness Comma12.1 avg_wellness Comma12.;

2-In total raw I want to add "Total" syntax

Data aaa;
Input lakID Cat \$ Wellness;
Cards;
1 A 10
2 A 30
3 B 20
4 A 60
5 B 70
6 B 20
7 A 50
8 A 100
9 A 40
10 A 150
;
Run;

proc summary data=aaa ;
class Cat;
var Wellness;
output out=pelet(rename=(_FREQ_=Lak)) sum=sum_wellness mean=avg_wellness;
Format  _FREQ_  COMMA12.  sum_wellness Comma12.1 avg_wellness Comma12.;
run;

Posts: 2,856

Re: freq and sum and average per category

@Ronein wrote:

Thanks.

2 problems:

1- I want to define format

Format  _FREQ_  COMMA12.  sum_wellness Comma12.1 avg_wellness Comma12.;

2-In total raw I want to add "Total" syntax

1 — Assign the formats when you use data set PELET after PROC SUMMARY

2 — I don't understand this at all, what do you mean?

--
Paige Miller
Frequent Contributor
Posts: 107

Re: freq and sum and average per category

I know to do it in 2 step

My question is how to do it in one step only?

Data aaa;
Input lakID Cat \$ Wellness ;
Cards;
1 A 10
2 A 30
3 B 20
4 A 60
5 B 70
6 B 2000
7 A 50
8 A 100
9 A 40
10 A 1500
;
Run;

/*Step1*/

proc summary data=aaa ;
class Cat;
var Wellness;
output out=pelet(rename=(_FREQ_=Lak)) sum=sum_wellness mean=avg_wellness;
/*Format sum_wellness Comma12.1 avg_wellness Comma12.;*/
run;

/*Step2*/
Data pelet2;
Set pelet;
IF cat='' then cat='Total';
Format sum_wellness avg_wellness Comma12.;
Run;

Posts: 2,856

Re: freq and sum and average per category

@Ronein wrote:

I know to do it in 2 step

My question is how to do it in one step only?

I don't think you can do it in one step.

--
Paige Miller
Super User
Posts: 13,365

Re: freq and sum and average per category

Perhaps you do really just want a report?

```proc tabulate data=work.aaa;
class cat;
var wellness;
table cat all='Total',
wellness*(n='Freq'*f=comma12. sum*f=comma12.1 mean='Average'*f=comma12.)
/
;
run;```

Note that if you have multiple variables that want a similar summary you would 1) add them to the VAR statement and the tables such as:

```proc tabulate data=work.aaa;
class cat;
var wellness niceness otherness;
table cat all='Total',
(wellness niceness otherness)*(n='Freq'*f=comma12. sum*f=comma12.1 mean='Average'*f=comma12.)
/
;
run;```
PROC Star
Posts: 1,605

Re: freq and sum and average per category

``````Data aaa;
Input lakID Cat \$ Wellness;
Cards;
1 A 10
2 A 30
3 B 20
4 A 60
5 B 70
6 B 20
7 A 50
8 A 100
9 A 40
10 A 150
;
Run;

data _null_;
if _n_=1 then do;
dcl hash H () ;
h.definekey  ("cat") ;
h.definedata ("cat","freq","Sum_Wellness", "Avg_Wellness") ;
h.definedone () ;
declare hiter iter('h');
end;
set aaa end=last;
call missing(Avg_Wellness);
if h.check() ne 0 then do;
freq=1;
Sum_Wellness=sum(Sum_Wellness,Wellness);
Avg_Wellness=Sum_Wellness/freq;
h.replace();
end;
else do;
h.find();
freq=freq+1;
Sum_Wellness=sum(Sum_Wellness,Wellness);
Avg_Wellness=Sum_Wellness/freq;
h.replace();
end;
_freq+1;
if last then do;
rc = iter.first();
do while (rc = 0);
_Sum_Wellness+Sum_Wellness;
rc = iter.next();
end;
cat='all';
freq=_freq;
Sum_Wellness=_Sum_Wellness;
Avg_Wellness=divide(Sum_Wellness,freq);