topic Re: Subtract from Previous Observation for a group in SAS Programming

Subtract from Previous Observation for a group

david27 — Thu, 17 Nov 2022 19:05:37 GMT

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;

Re: Subtract from Previous Observation for a group

PaigeMiller — Thu, 17 Nov 2022 19:09:58 GMT

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

Re: Subtract from Previous Observation for a group

PeterClemmensen — Thu, 17 Nov 2022 19:12:42 GMT

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?

Re: Subtract from Previous Observation for a group

Kurt_Bremser — Thu, 17 Nov 2022 19:14:32 GMT

What about

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

Re: Subtract from Previous Observation for a group

david27 — Thu, 17 Nov 2022 19:29:48 GMT

@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.htm

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() ?

Re: Subtract from Previous Observation for a group

PaigeMiller — Thu, 17 Nov 2022 19:36:59 GMT

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.

Re: Subtract from Previous Observation for a group

andreas_lds — Fri, 18 Nov 2022 06:43:27 GMT

@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.

Re: Subtract from Previous Observation for a group

mkeintz — Sun, 20 Nov 2022 04:16:33 GMT

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.