SAS query

[NTR]

good morning guys, I would really appreciate some assistance. I have a record of monthly accounts from 2012 to 2018 tracking their performance. what I need to do is to identify when the account first defaulted which I managed to do. Now I need help with counting the number of months since default. how do I go about doing that. on SAS...in the below example months since default is 10

example of data

Date      Act number  Default Flag

201201 5203370   N
201202 5203370  N
201203 5203370  N
201204 5203370  N
201205 5203370  N
201206 5203370 Y
201207 5203370  N
201208 5203370  N
201209 5203370  N
201210 5203370 N
201211 5203370  N
201212 5203370 N
201301 5203370 N
201302 5203370 N
201303 5203370 N
201304 5203370 N

[jklaverstijn]

You can calculate the duration of an interval with the INTCK() function. Assuming that your variable Date is truly a date (numeric), you would use it like

Months = INTCK('MONTH',Date,today())

If Date is a character (as I suspect; its header is left aligned in your preview) you will first have to convert:

Date_num= input(Date, YYMMN6.);

These two lines can be compounded as

Months = INTCK('MONTH',input(Date, YYMMN6.), today());

Hope this helps,

- Jan.

[Kurt_Bremser]

You do that by using a retained variable:

data have;
input date $ act_number $ default_flag $;
cards;
201201 5203370 N
201202 5203370 N
201203 5203370 N
201204 5203370 N
201205 5203370 N
201206 5203370 Y
201207 5203370 N
201208 5203370 N
201209 5203370 N
201210 5203370 N
201211 5203370 N
201212 5203370 N
201301 5203370 N
201302 5203370 N
201303 5203370 N
201304 5203370 N
;
run;

data want;
set have;
retain count .;
if count ne . then count + 1;
if default_flag = 'Y' then count = 0;
run;

Note how I presented example data in a data step. Anybody can copy/paste the code, run it, and will get the same dataset.

If you need to run this for different accounts, you need to use by-group processing and reset your counter to missing at first.account.

[jklaverstijn]

Ah yes looks like I completely misread the question.

[NTR]

hi Kurt, thanks for this....I'm not sure if I get you. you mean I need to

data want;
set have;

by act_number;

if first.act_number then do;
retain count .;

if count ne . then count + 1;
if default_flag = 'Y' then count = 0;

end;
run;

looking forward to hear from you.

[Kurt_Bremser · Accepted solution]

---

@NTR wrote:

hi Kurt, thanks for this....I'm not sure if I get you. you mean I need to

data want;
set have;

by act_number;

if first.act_number then do;
retain count .;

if count ne . then count + 1;
if default_flag = 'Y' then count = 0;

end;
run;

looking forward to hear from you.

---

When you have to work with groups, don't use the retain statement for initialization.

Do it with the first. automatic variable:

data want;
set have;
by act_number;
retain count;
if first.act_number then count = .;
if count ne . then count + 1;
if default_flag = 'Y' then count = 0;
run;

[NTR]

thanks Kurt. it worked perfectly. One last question. how do I keep the default date  in all observations after default?

[Patrick]

Counting months will work as long as you can be 100% sure that there won't be any missing records (a month missing) in your data. It shouldn't with financial data but just in case below a coding variant which will also work if there are missing months.

data have;
  input date :yymmn6. act_number $ default_flag $;
  format date date9.;
  cards;
201201 5203370 N
201202 5203370 N
201203 5203370 N
201204 5203370 N
201205 5203370 N
201206 5203370 Y
201207 5203370 N
201208 5203370 N
201209 5203370 N
201210 5203370 N
201211 5203370 N
201212 5203370 N
201301 5203370 N
201302 5203370 N
201303 5203370 N
201304 5203370 N
;
run;

data want;
  if _n_=1 then
    do;
      dcl hash defFlg(dataset:'have(keep=act_number date default_flag rename=(date=defDate) where=(default_flag="Y"))');
      defFlg.defineKey('act_number');
      defFlg.defineData('defDate');
      defFlg.defineDone();
    end;
  set have;
  
  if defFLG.find()=0 then
    do;
      month=intck('month',defDate,date);
    end;
run;

N.B: Above code doesn't require dataset Have to be sorted. It does require though that each account doesn't have more than one default month and that variable date contains a SAS date value so we can use SAS calendar functions like intck().