BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tbeaty2
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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