Duplicate entries but variable of interest on both rows

[buder]

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?

[Astounding]

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]

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

Thanks so much!