topic Re: Calculate column values based on the same column's lagged cumulative sums in New SAS User

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

cmemtsa — Tue, 06 Oct 2020 06:30:25 GMT

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

Thank you in advance for your help.

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

Kurt_Bremser — Tue, 06 Oct 2020 06:37:40 GMT

There is no column MV in your dataset.

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

cmemtsa — Tue, 06 Oct 2020 06:42:58 GMT

Apologies, it's the column M

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

Kurt_Bremser — Tue, 06 Oct 2020 06:54:53 GMT

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;

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

cmemtsa — Tue, 06 Oct 2020 07:03:47 GMT

Thanks! It worked!