pynewbee pynewbee - 3 months ago 11
SQL Question

how to find the lag of the first observation in a grouped data in SAS

I have a dataset,and it looks like this:

id name score

1 daniel 30
1 jane 20
1 keisha 70
2 kelly 30
2 jerry 60
2 jay 40


i want to find the difference between scores, comparing the FIRST score of each group to all other scores in that group. So for example,

i want to compare jane's score to daniel's (30-20=10) and compare keisha's score to daniel's (abs(30-70) = 40)

And to start over by comparing jerry's score to kelly's (abs(30-60=30)) and jay's score to kelly's (40-30 = 10).

id name score compare

1 daniel 30 .
1 jane 20 10
1 keisha 70 40
2 kelly 30 .
2 jerry 60 30
2 jay 40 10


Does anybody know of a way to write this in SAS? or any SQL command?

I've tried the following

data scoring_prep;
set scoring_prep;
by id;
if not missing(score) then do;
scorediff = abs(dif(score));
if id ne lag(id) then scorediff = .;
end;
run;


but this only find provides lag of the previous record, so keisha, for example, will be compared with jane instead of daniel.

Tom Tom
Answer

Since you are not comparing to the previous value you do not want to use the LAG() or DIF() function. Instead use a retained variable to carry to comparison value forward.

data want;
  set scoring_prep;
  by id;
  retain baseline;
  if first.id then baseline=score;
  else scorediff=abs(baseline - score);
run;