SAS query

Accepted Solution Solved
Reply
Occasional Contributor NTR
Occasional Contributor
Posts: 7
Accepted Solution

SAS query

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


Accepted Solutions
Solution
‎03-14-2018 08:09 AM
Super User
Posts: 10,280

Re: SAS query


@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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Valued Guide
Posts: 534

Re: SAS query

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.

Super User
Posts: 10,280

Re: SAS query

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 534

Re: SAS query

Posted in reply to KurtBremser
Ah yes looks like I completely misread the question.
Occasional Contributor NTR
Occasional Contributor
Posts: 7

Re: SAS query

Posted in reply to KurtBremser

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.

Solution
‎03-14-2018 08:09 AM
Super User
Posts: 10,280

Re: SAS query


@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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor NTR
Occasional Contributor
Posts: 7

Re: SAS query

Posted in reply to KurtBremser

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

Respected Advisor
Posts: 4,742

Re: SAS query

[ Edited ]

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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