topic Re: Difference between date in MONYY format in SAS Programming

Difference between date in MONYY format

PDevi — Tue, 29 Oct 2019 22:35:21 GMT

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.

Re: Difference between date in MONYY format

Reeza — Wed, 30 Oct 2019 01:19:59 GMT

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

Re: Difference between date in MONYY format

mkeintz — Tue, 29 Oct 2019 22:45:32 GMT

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.

Re: Difference between date in MONYY format

novinosrin — Tue, 29 Oct 2019 22:56:14 GMT

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;

Re: Difference between date in MONYY format

PDevi — Wed, 30 Oct 2019 01:38:08 GMT

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

Re: Difference between date in MONYY format

Reeza — Wed, 30 Oct 2019 02:06:43 GMT

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.

Re: Difference between date in MONYY format

Jagadishkatam — Wed, 30 Oct 2019 06:05:26 GMT

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
;

Re: Difference between date in MONYY format

PDevi — Wed, 30 Oct 2019 15:27:36 GMT

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

Re: Difference between date in MONYY format

PDevi — Wed, 30 Oct 2019 17:18:02 GMT

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

Re: Difference between date in MONYY format

PDevi — Wed, 30 Oct 2019 17:18:39 GMT

@Jagadishkatam Thank you.

Re: Difference between date in MONYY format

PDevi — Wed, 30 Oct 2019 17:19:24 GMT

@mkeintz Thanks