Solved
New Contributor
Posts: 2

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?

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

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

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

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,698

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.