topic Duplicate entries but variable of interest on both rows in SAS Data Management

Duplicate entries but variable of interest on both rows

buder — Thu, 26 May 2016 23:48:06 GMT

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?

Re: Duplicate entries but variable of interest on both rows

Astounding — Fri, 27 May 2016 00:08:04 GMT

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.

Re: Duplicate entries but variable of interest on both rows

buder — Fri, 27 May 2016 00:10:50 GMT

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

Thanks so much!