Subtract from Previous Observation for a group

[david27]

Hello,

I have this:

I want to subtract the new score for each player from the previous score of that player.

In case if the player has only one record then leave the calculated field blank.

Please advise.

Thank You

data have;
infile datalines dlm="|";
input id score;
datalines;
1|1
2|3
2|1
2|3
2|3
3|11
4|11
5|1
5|1
6|1
7|11
8|1
9|11
10|11
10|11
;run;

[PaigeMiller]

data want;
    set have;
    by id;
    prev_score=lag(score);
    if first.id then prev_score=.;
    difference=score-prev_score;
    drop prev_score;
run;

[Kurt_Bremser]

What about

difference = score - lag(score);
if first.id then difference = .;

?

[david27]

@PaigeMiller : 

Thank You for helping me here.

This solves the purpose but have a follow up question because of this:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/n0l66p5oqex1f2n1quuopdvtcjqb.h...

Restriction:Note:

This function is not supported in a DATA step that runs in CAS.

The VARCHAR type is not supported for arguments in the LAG function.

So if the Score was provided in Character format, we cannot use lag() ?

[PaigeMiller]

Well, you probably should have mentioned in your first post that you are running in CAS.

I don't have any knowledge of how to get this to work in CAS. It is my understanding that LAG is not available in CAS. I know you can run code in SAS Viya that is not run under CAS, in other words, it is base SAS, but I don't know how to do that either.

Yes, score needs to be numeric to get this to work in any version of SAS. You can't do a subtraction on character values.

[andreas_lds]

---

@david27 wrote:

So if the Score was provided in Character format, we cannot use lag() ?

---

If score is a char variable the subtraction makes no sense at all. You will have to convert the variable to numeric before any calculation takes place. 

[mkeintz]

You can simulate the lag function with a retain statement:

data have;
infile datalines dlm="|";
input id score;
datalines;
1|1
2|3
2|1
2|3
2|3
3|11
4|11
5|1
5|1
6|1
7|11
8|1
9|11
10|11
10|11
;run;

data want;
  set have;
  by id;
  retain old_score .;
  if first.id then old_score=.;
  if old_score^=. then difference=score-old_score;
  output;

  old_score=score;
run;

The "trick" here is to output the current observation prior to updating the (retained) old_score variable with the current score value.

[PeterClemmensen]

I'm a bit confused here. Do you want to subtract the new score from the previous score or the previous score from the new score? The latter seems to make most sense?