BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;


8 REPLIES 8
FredrikE
Rhodochrosite | Level 12

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

//Fredrik

Ronein
Meteorite | Level 14

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
Diamond | Level 26

@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
Ronein
Meteorite | Level 14

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
Diamond | Level 26

@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
ballardw
Super User

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
Tourmaline | Level 20
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 832 views
  • 2 likes
  • 6 in conversation