Use previous value with a maximum cap of each ID

[saf_nadia]

I have data like this:
Id date cb
X 1jan 27
Y 1jan 47
X 2jan 10
Y 2jan 13
X 3jan 37

Each Id have a cap of 50 for total cb (let cb2)
I want an output to be like this:

Id date Cb Cb2
X 1jan 27 27
Y 1jan 47 47
X 2jan 10 10
Y 2jan 13 3
X 3jan 37 13

Hope you guys can help

[mkeintz]

I think I understand that new variable CB2 should never exceed 50, but I don't understand any other rule you have in mind for CB2.

What are your rules of constructing CB2?

[saf_nadia]

I need cb2 to calculate the total of the whole dataset so that the total does not exceed the limit (let say the total of all cb2 should less than 100).

[mkeintz]

OK, I get it now.  For each ID, you want to maintain a cumulative sum of CB.  As long as the sum is <= 50, CB2=CB. But if it exceeds 50, then CB2 is the value that brings the sum to exactly 50.

If your data were sorted by ID/DATE, you could just keep a cumulative sum (CBSUM) to guide your assignment of CB2.Then resort to original order (by DATE/ID):

data have;
input Id $ date date9. cb;
format date date9.;
datalines;
X 1jan2018 27
Y 1jan2018 47
X 2jan2018 10
Y 2jan2018 13
X 3jan2018 37
Y 3jan2018 11
run;


proc sort data=have; 
  by id date;
run;
data want (drop=cbsum);
  set have;
  by id;
  if first.id then cbsum=0;
  if cbsum>50 then cb2=0;
  else cb2=min(50-cbsum,cb);
  cbsum+cb;
run;
proc sort;
  by date id;
run;

Note that CBSUM is retained across observations, due to the "summing statement" ( CBSUM+CB).

If you want to avoid sorting then you could keep a CBSUM value for each ID, in a hash object:

data want (drop=cbsum);
  set have;
  if _n_=1 then do;
    declare hash h ();
      h.definekey('id');
      h.definedata('cbsum');
      h.definedone();
  end;
  if h.find()^=0 then cbsum=0;
  if cbsum>50 then cb2=0;
  else cb2=min(50-cbsum,cb);
  cbsum+cb;
  h.replace();
run;

[PGStats · Accepted solution]

You mean, something like this?

data have;
input Id $ date :date9. cb;
format date date5.;
datalines;
X 1jan2019 27
Y 1jan2019 47
X 2jan2019 10
Y 2jan2019 13
X 3jan2019 37
;

proc sort data=have; by id date; run;

%let limit=50;

data want;
s = 0;
do until(last.id);
    set have; by id;
    cb2 = max(0, min(cb, &limit. - s)); 
    s = s + cb;
    output;
    end;
drop s;
run;

proc sort data=want; by date id; run;

proc print data=want noobs; run;

[saf_nadia]

Thanks a lot for your reply! The code works really well!