topic Re: Use previous value with a maximum cap of each ID in SAS Programming

Use previous value with a maximum cap of each ID

saf_nadia — Tue, 15 Jan 2019 04:50:34 GMT

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

Re: Use previous value with a maximum cap of each ID

mkeintz — Tue, 15 Jan 2019 05:00:36 GMT

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?

Re: Use previous value with a maximum cap of each ID

saf_nadia — Tue, 15 Jan 2019 05:04:40 GMT

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

Re: Use previous value with a maximum cap of each ID

PGStats — Tue, 15 Jan 2019 05:40:14 GMT

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;

Re: Use previous value with a maximum cap of each ID

mkeintz — Tue, 15 Jan 2019 05:52:13 GMT

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;

Re: Use previous value with a maximum cap of each ID

saf_nadia — Tue, 15 Jan 2019 06:19:41 GMT

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