CODEWITHSUNDEEP
mysqlsqlsaslag
pynewbee
pynewbee

Reputation: 679

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.

Upvotes: 1

Views: 300

Answers (1)

Tom
Tom

Reputation: 51601

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 the comparison value forward.

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

Upvotes: 4

Related Questions