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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.