topic Re: freq and sum and average per category in SAS Programming

freq and sum and average per category

Ronein — Wed, 09 May 2018 10:25:16 GMT

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;

Re: freq and sum and average per category

Kurt_Bremser — Wed, 09 May 2018 10:29:03 GMT

proc summary is all you need.

Re: freq and sum and average per category

FredrikE — Wed, 09 May 2018 10:32:09 GMT

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

//Fredrik

Re: freq and sum and average per category

Ronein — Wed, 09 May 2018 11:51:25 GMT

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;

Re: freq and sum and average per category

PaigeMiller — Wed, 09 May 2018 12:06:41 GMT

@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?

Re: freq and sum and average per category

Ronein — Wed, 09 May 2018 12:15:34 GMT

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;

Re: freq and sum and average per category

PaigeMiller — Wed, 09 May 2018 12:27:08 GMT

@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.

Re: freq and sum and average per category

novinosrin — Wed, 09 May 2018 14:56:50 GMT

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);
h.add();
h.output(dataset:'want');
end;
run;

Re: freq and sum and average per category

ballardw — Wed, 09 May 2018 15:36:09 GMT

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;