SAS calulations with multiple observations

0
votes

I'm trying to compute a value across observations. My data look like

ID LAND DECILE DISTANCE  
1    85   1       1.5  
1    85   2       3.4  
.  
.  
1    85   9       13.2  
2    76   1       0.9
2    76   2       2.7

I'm trying to calculate for each ID with ten deciles, (decile10 - decile(i+1))/(decile10 - decile1), where i is the ith decile. I'm not worried about decile 10. Thanks for any help! Been struggling with this all day.

1
sas

1 Answers

1
votes

Simple way to get multiple things on the dataset: merge. Here we merge the 10th decile onto all 10 deciles, and use retain to keep the 1st decile value. There are other approaches that work if sorting is a difficulty, but this is the simplest if your data aren't insanely large.

proc sort data=have;
  by id decile;
run;

data merged;
  merge have have(where=(decile=10) rename=(distance=distance_10 decile=decile10) keep=id decile distance);
  by id;
  retain distance_1;
  if first.id then distance_1=distance; *assuming sorted by ID then decile;
  ... your calculations, using distance_1 and distance_10 ...
run;