freq and sum and average per category

[Ronein]

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;

[Kurt_Bremser]

proc summary is all you need.

[FredrikE]

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

//Fredrik

[Ronein]

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;

[PaigeMiller]

---

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

[Ronein]

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;

[PaigeMiller]

---

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

[ballardw]

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;

[novinosrin]

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;