DATA Step, Macro, Functions and more

freq and sum and average per category

Reply
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 Smiley Happy

//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;

Respected Advisor
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

Posted in reply to PaigeMiller

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;

Respected Advisor
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);
h.add();
h.output(dataset:'want');
end;
run;
Ask a Question
Discussion stats
  • 8 replies
  • 122 views
  • 2 likes
  • 6 in conversation