BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NTR
Fluorite | Level 6 NTR
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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;

View solution in original post

7 REPLIES 7
jklaverstijn
Rhodochrosite | Level 12

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
Super User

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
Rhodochrosite | Level 12
Ah yes looks like I completely misread the question.
NTR
Fluorite | Level 6 NTR
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6 NTR
Fluorite | Level 6

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

Patrick
Opal | Level 21

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1756 views
  • 0 likes
  • 4 in conversation