How do I calculate quarterly averages using selected observations

[tbeaty2]

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!

[Reeza · Accepted solution]

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

[Ksharp]

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;

[tbeaty2]

Thank you. both!