topic Decrease by 1 in SAS Enterprise Guide

Decrease by 1

Shamz — Fri, 23 Oct 2020 12:29:30 GMT

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;

Re: Decrease by 1

Kurt_Bremser — Fri, 23 Oct 2020 14:23:41 GMT

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.

Re: Decrease by 1

FreelanceReinh — Fri, 23 Oct 2020 15:52:41 GMT

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;

Re: Decrease by 1

Shamz — Sat, 24 Oct 2020 17:33:15 GMT

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 😊😊😊

Re: Decrease by 1

Shamz — Sat, 24 Oct 2020 17:36:21 GMT

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.