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!
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 ....
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 ....
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;
Thank you. both!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.