Difference between date in MONYY format

[PDevi]

Hello,

I have a table that has two dates as below:

id      appl_date          doc_date       

1      SEP2013           AUG2013

2      JAN2019           NOV2018

3      MAY2017          JAN2015

The 'appl_date' is a character field and the 'doc_date' is a date field in MONYY7. format. I need to keep only those records that have a gap of 2 or less years. In the above example, I should be excluding id #3.

Can someone please help?

Thanks in advance.

[Reeza]

You need to convert your appl_date to a sas date.
Assuming the day doesn't matter, use :

dif = intck('year', input(appl_date, monyy5.), doc_date);

[PDevi]

@Reeza Hi Reeza, this works except in those cases where the difference is in months. Say the appl_date is 'MAY2019' and the doc_date is 'APR2019', then it returns 0. How can I address such cases?

[Reeza]

You asked for two years or less, 0 is less than 2 and it hasn't completed a year so not sure how that doesn't answer your question.

If you want months instead, switch the interval from year to months and change your criteria to be 24 months instead.

[PDevi]

You are right, I should have been more explicit in what I was looking for. Thanks @Reeza

[mkeintz]

So, you need to make a new variable (call it APPDATE) with the numeric date value corresponding to the text in APPL_DATE.  To be

a date value you need more than SEP2013, which is missing the day number.   So you can prepend a "01" to APPL_DATE, and then apply the INPUT function to that string using the DATE9. informat.

Then you can compare using the SAS INTCK function to count the number of months between APPDATE and DOC_DATE as your  filter mechanism.

[PDevi]

@mkeintz Thanks

[novinosrin · Accepted solution]

data have;
 input id      (appl_date          doc_date ) (:monyy7.);
 format  appl_date          doc_date monyy7.;
cards;
1      SEP2013           AUG2013
2      JAN2019           NOV2018
3      MAY2017          JAN2015
;

data want;
set have;
if yrdif(doc_date,appl_date,'ACT/ACT')<=2;
run;

Oh your is char field, so

data have;
 input id      (appl_date          doc_date ) ($);
cards;
1      SEP2013           AUG2013
2      JAN2019           NOV2018
3      MAY2017          JAN2015
;

data want;
set have;
if yrdif(input(doc_date,monyy7.),input(appl_date,monyy7.),'ACT/ACT')<=2;
run;

[PDevi]

@novinosrin Thanks. This worked perfectly for what I was looking to do.

[Jagadishkatam]

Please try the below code as well,

data have;
input id appl_date$ doc_date:date9. ;
if not (intck('year', input(cats('01',appl_date), date9.),doc_date ) <=-2);
format  doc_date monyy7.;
cards;      
1 SEP2013 01AUG2013
2 JAN2019 01NOV2018
3 MAY2017 01JAN2015
;

[PDevi]

@Jagadishkatam Thank you.