topic Re: Calculation based on lag in SAS Programming

Calculation based on lag

SAS_Muggle — Sun, 31 Mar 2024 22:36:57 GMT

I have some data like the below:

Group Sort Value

1 1 50

1 2 55

1 3 60

1 4 70

2 1 40

2 2 60

2 3 40

2 4 90

I want to create 2 new variables x and y such that if sort=1 then x=0 and y=value, otherwise x=value from previous row y and y= x + value, so like below:

Group Sort Value x y

1 1 50 0 50

1 2 55 50 105

1 3 60 105 165

1 4 70 165 135

2 1 40 0 40

2 2 60 40 100

2 3 40 100 140

Can anyone help with code. Whenever I run using lag and by sort and group, the new y does update based on the previous calculation.

Re: Calculation based on lag

WarrenKuhfeld — Sun, 31 Mar 2024 23:25:48 GMT

Be sure to create the lag unconditionally, even if you use the value conditionally.

Be sure to read the documentation. If you have expectations on the behavior of lag as a matrix operator, you will find that they don't apply to a function in a general language like the SAS DATA step.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/n0l66p5oqex1f2n1quuopdvtcjqb.htm

Re: Calculation based on lag

Ksharp — Mon, 01 Apr 2024 01:38:11 GMT

data have;
input Group Sort Value; 
cards;
1 1 50
1 2 55
1 3 60
1 4 70
2 1 40
2 2 60
2 3 40
2 4 90
;
data want;
 set have;
 retain _y;
 if sort=1 then do;x=0;y=value;_y=value;end;
  else do;x=_y;y=x+value;_y=y; end;
 drop _y;
run;

Re: Calculation based on lag

mkeintz — Mon, 01 Apr 2024 02:11:50 GMT

Do you really want to check for SORT=1, or check for start of a new group. Of course they are the same if SORT=1 is reliably the first (and only) observation of each incoming group. There is a way to use LAG, as here:

data have;
input Group Sort Value  ; 
cards;
1 1 50
1 2 55
1 3 60
1 4 70
2 1 40
2 2 60
2 3 40
2 4 90
;run;

data want;
  set have;
  by group;
  x+lag(value);  if first.group then x=0;
  y+value;       if first.group then y=value;
run;

Of course you could use "IF sort=1" instead of "IF first.group".