DATA Step, Macro, Functions and more

How do I calculate quarterly averages using selected observations

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How do I calculate quarterly averages using selected observations

[ Edited ]

Hello!

 

I am a fairly new SAS user, so forgive me if the solution is easy. I am using SAS 9.4.

 

I have a dataset which contains longitudinal data over several years, with multiple observations for each ID. I've coded in what quarter of the year the observation took place. It looks like this:

 

ID Date Variable Quarter

A  xxxx  103        1

A  xxxx  91          1

A  xxxx  90          3

B  xxxx 98           1

B  xxxx  97          2

B  xxxx  80          2

B  xxxx  75          3

B  xxxx  70          4

 

The goal is to take the highest value of each quarter per subject and calculate their average. Thus, for B it would be [(98+97+75+70)/4)] - the 80 in quarter two should not be calculated). As you can see for A, the preferred value would be [(103+90)/2].

 

What I had thought was to create a multitude of datasets bringing in the highest value for each quarter, then merge them together to do the calculation, but I am sure there is an easier way to do it. How would you do this?

 

Thank you in advance!


Accepted Solutions
Solution
‎04-08-2017 10:48 AM
Super User
Posts: 19,878

Re: How do I calculate quarterly averages using selected observations

Sort with NODUPKEY option, then use PROC MEANS

 

Proc sort data= have; by Id qtr descending variable;

proc sort data=have out = unique NODUPKEY; by ID QTR; run;

 

proc means ....

 

View solution in original post


All Replies
Solution
‎04-08-2017 10:48 AM
Super User
Posts: 19,878

Re: How do I calculate quarterly averages using selected observations

Sort with NODUPKEY option, then use PROC MEANS

 

Proc sort data= have; by Id qtr descending variable;

proc sort data=have out = unique NODUPKEY; by ID QTR; run;

 

proc means ....

 

Super User
Posts: 10,048

Re: How do I calculate quarterly averages using selected observations

data have;
input ID $ Date $ Variable Quarter;
cards;
A  xxxx  103        1
A  xxxx  91          1
A  xxxx  90          3
B  xxxx 98           1
B  xxxx  97          2
B  xxxx  80          2
B  xxxx  75          3
B  xxxx  70          4
;
run;
proc sql;
create table want as
select id,mean(v) as mean
 from
 (select id,quarter,max(variable) as v from have group by id,quarter)
   group by id;
quit;
New Contributor
Posts: 2

Re: How do I calculate quarterly averages using selected observations

Thank you. both!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 271 views
  • 2 likes
  • 3 in conversation