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;
Use proc summary and set output sum=sum_wellness and mean=avg_wellness 🙂
//Fredrik
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;
@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?
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;
@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.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.