BookmarkSubscribeRSS Feed
buder
Fluorite | Level 6

I have a dataset where I want to delete the duplicate entries but the problem is that even though I have a person in there twice with identical demographic characteristics, my variable of interest needs to be averaged out and then I want to delete the second row. 

 

The data set looks as follows:

 
PersonID   Year                  Age            EXP
     1            2002                44            1000
     1            2003                45             200
   
 
I would like to create a new variable that is an average of EXP - is that possible when they are on two different rows? 
 
I tried to create a separate variable for EXP for each year; labeled them EXP01 and EXP02 after merging (then could take the average): 
 
So the column became as such: 
 
PersonID   Year                 Age              EXP    EXP01   EXP02
     1            2002                44            1000        1000     0
     1            2003                45             200          0           200         
 
 
Then I tried to add up EXP01 and EXP02 but since they are not on the same row, it wouldn't compute. So I'm struggling with how to get that '200' from EXP02 to be on the first row.
 
Any suggestions?

 

2 REPLIES 2
Astounding
PROC Star

Do you actually want all those variables?  SAS has plenty of ways to compute an average.  

 

proc sort data=have;

by PersonID;

run;

 

proc summary data=have;

by PersonID;

output out=means (keep=PersonID MeanEXP) mean=MeanEXP;

var EXP;

run;

 

data want;

merge have means;

by PersonID;

if first.PersonID;

run;

 

You don't have to merge the two together ... you could keep the two data sets separate if that fits your needs better.

 

Good luck.

buder
Fluorite | Level 6

You rock - been coding all day and could not figure out how to deal with this.

 

Thanks so much!

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 809 views
  • 2 likes
  • 2 in conversation