topic Re: SAS query in SAS Programming

SAS query

NTR — Wed, 14 Mar 2018 07:36:38 GMT

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

Re: SAS query

jklaverstijn — Wed, 14 Mar 2018 07:51:48 GMT

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.

Re: SAS query

Kurt_Bremser — Wed, 14 Mar 2018 07:55:07 GMT

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.

Re: SAS query

jklaverstijn — Wed, 14 Mar 2018 07:58:29 GMT

Ah yes looks like I completely misread the question.

Re: SAS query

NTR — Wed, 14 Mar 2018 09:16:55 GMT

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.

Re: SAS query

Patrick — Wed, 14 Mar 2018 09:36:37 GMT

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

Re: SAS query

Kurt_Bremser — Wed, 14 Mar 2018 10:08:59 GMT

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

Re: SAS query

NTR — Wed, 14 Mar 2018 12:08:51 GMT

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