topic How do I calculate quarterly averages using selected observations in SAS Programming

How do I calculate quarterly averages using selected observations

tbeaty2 — Sat, 01 Apr 2017 22:53:08 GMT

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!

Re: How do I calculate quarterly averages using selected observations

Reeza — Sat, 01 Apr 2017 22:59:13 GMT

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

Re: How do I calculate quarterly averages using selected observations

Ksharp — Sun, 02 Apr 2017 02:56:47 GMT

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;

Re: How do I calculate quarterly averages using selected observations

tbeaty2 — Mon, 03 Apr 2017 07:05:31 GMT

Thank you. both!