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
@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;
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.
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.
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.
@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;
thanks Kurt. it worked perfectly. One last question. how do I keep the default date in all observations after default?
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().
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.