Decrease by 1

[Shamz]

Hi everyone...does anyone know how should i modify my coding to get result that generates after d_DELQ_BUCKET2=74, the next row must be d_DELQ_BUCKET2=73,...72,...71 and so on.
However the example here only one sample from 90,000 records. Appreciate if the coding can applicable to large records. Thanks a lot

My coding:
Data a;
Set b;
by acct_number;
If first. acct_number then d_DELQ_BUCKET=DELINQUENCY_BUCKET;
else d_DELQ_BUCKET+(-1);
d_DELQ_BUCKET2=max(DELINQUENCY_BUCKET, d_DELQ_BUCKET)
run;

[Kurt_Bremser]

Why are you doing that MAX calculation, when you want a strictly decreasing sequence?

Please post example data in usable form, as a data step with datalines, and use the "little running man" button to post the code. One can't develop and test code against pictures. And post an example for the expected output from that example data.

[Shamz]

Hi this is my first time posting here so i dont know how to post but thank you for your guidance on how i should ask the question.

[FreelanceReinh · Accepted solution]

Hi @Shamz and welcome to the SAS Support Communities!

Your sample data suggest that the values of DELINQUENCY_BUCKET within an acct_number BY group (which is not shown, though) are decreasing, except for one (or more?) observation. What should happen if DELINQUENCY_BUCKET has a second (third, ...) increase, e.g., to 72 after the last record shown in your screenshot?

If d_DELQ_BUCKET2 is to ignore these later increases (analogous to d_DELQ_BUCKET), I would suggest something like this:

data a(drop=_flag);
set b;
by acct_number;
if first.acct_number then do;
  d_DELQ_BUCKET  = DELINQUENCY_BUCKET;
  d_DELQ_BUCKET2 = DELINQUENCY_BUCKET;
  _flag+1;
end;
else do;
  d_DELQ_BUCKET+(-1);
  if DELINQUENCY_BUCKET>d_DELQ_BUCKET & _flag then do;
    d_DELQ_BUCKET2 = DELINQUENCY_BUCKET;
    _flag=0;
  end;
  else d_DELQ_BUCKET2+(-1);
end;
run;

However, if d_DELQ_BUCKET2 is to be "reset" also in the event of second, third, ... increases or if no such cases can occur anyway, I'd suggest:

data a;
set b;
by acct_number;
if first.acct_number then do;
  d_DELQ_BUCKET  = DELINQUENCY_BUCKET;
  d_DELQ_BUCKET2 = DELINQUENCY_BUCKET;
end;
else do;
  d_DELQ_BUCKET+(-1);
  if DELINQUENCY_BUCKET>d_DELQ_BUCKET2 then d_DELQ_BUCKET2 = DELINQUENCY_BUCKET;
  else d_DELQ_BUCKET2+(-1);
end;
run;

[Shamz]

Hi, the answer in option 2 is the solution i need... Thanks a lot for your help and you are so geniusss and may god bless you 😊😊😊