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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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