Calculate column values based on the same column's lagged cumulative sums

[cmemtsa]

data T;
input ID L M EXP;
Datalines;
1 1 100 10
1 2 100 20
1 2 100 20
1 3 100 30
2 1 50 10
2 1 50 20
2 2 50 60
;

Hi, based on this dataset I want to create a new dataset with an extra column (WANTED) which includes the following steps:

1. All calculations are per group ID

2. For each first ID occurrence, the value WANTED is equal to the value in Col EXP

3. For the second row and so on, the formula is IF L=1 then =EXP Else if (cumulative sum of previous values WANTED<MV) then MV-LAG(EXP) ELSE 0  

Finally the WANTED column should have the following values

10

90

0

0

10

20

30

Thank you in advance for your help.

[Kurt_Bremser · Accepted solution]

There is no column MV in your dataset.

[cmemtsa]

Apologies, it's the column M

[Kurt_Bremser]

Then I think this should do it:

data want;
set t;
by id;
l_exp = lag(exp); /* never call LAG() conditionally */
if first.id
then do;
  wanted = exp;
  sum_wanted = exp;
end;
else do;
  if l = 1
  then wanted = exp;
  else if sum_wanted < m
  then wanted = m - l_exp;
  else wanted = 0;
  sum_wanted + wanted; /* increment statement causes automatic retain */
end;
drop sum_wanted l_exp;
run;

[cmemtsa]

Thanks! It worked!