topic Re: Matching dates in SAS Programming

Matching dates

jeremy4 — Thu, 25 Jul 2019 15:07:01 GMT

Hi,

I am creating a proc sql table and would like to join these two variables by date can someone please help me with the line of code to join them together based on the relevant month (the screenshots are only an extract of the year, so I would like to match cohort_mth, where any day of the month would be recognised as just the month e.g. 01DEC2017 or 20DEC2017 would both be recognised as DEC2017, so that it matches with the mth_end variable, which is in the mmmyyyy format)?

This would mean that any date and month e.g. 01FEB2017 and 15FEB2017 from cohort_mth would both be recognised as FEB2017 and this would be matched with FEB2017 from mth_end.

I have already matched by ID with the line (on a.customer_ID = b.ID and a.cohort_mth=b.mth_end ) - can someone please edit the red text so that they are matched together, either by recognising that it is DEC2017 or numerically 201712)? Thanks!

Dataset A

Dataset B

Re: Matching dates

PaigeMiller — Thu, 25 Jul 2019 15:10:46 GMT

You can't do the matching using human readable dates. SAS matches dates by SAS date values, which are integers.

So to match 01FEB2017 and 15FEB2017, you have to turn them both into the SAS date value integer equivalent of Feb 1, 2017.

You can use INTNX function to do this.

In a SAS data set, you could use

converted_date=intnx('month',date,0,'b');

The 'b' at the end selects the first day of the month in the variable DATE.

In PROC SQL, you could use something like this to do the matching

on intnx('month',a.date,0,'b') eq intnx('month',b.date,0,'b')

Re: Matching dates

jeremy4 — Thu, 25 Jul 2019 15:19:31 GMT

Thanks for your reply!

Would that mean that in relation to my example, it would be:

on intnx('month',a.cohort_mth,0,'b') = intnx('month',b.mth_end,0,'b')

Would something like this also work to answer my question?

on input(put(a.cohort_mth,yymmn6.),6.) = input(put(b.mth_end,yymmn6.),6.)

Re: Matching dates

Tom — Thu, 25 Jul 2019 15:21:48 GMT

@jeremy4 wrote:

Thanks for your reply!

Would that mean that in relation to my example, it would be:

on intnx('month',a.cohort_mth,0,'b') = intnx('month',b.mth_end,0,'b')

Would something like this also work to answer my question?

on input(put(a.cohort_mth,yymmn6.),6.) = input(put(b.mth_end,yymmn6.),6.)

Yes. But there is no need for the extra INPUT() function. You can just compare the strings generated by the PUT() functions.

Re: Matching dates

jeremy4 — Thu, 25 Jul 2019 15:28:06 GMT

Thanks, can I confirm that the line of code would be:
on put(a.cohort_mth,yymmn6.),6.) = put(b.mth_end,yymmn6.),6.) and a.customer_ID = b.ID

Re: Matching dates

PaigeMiller — Thu, 25 Jul 2019 15:37:46 GMT

@jeremy4 wrote:

Thanks for your reply!

Would that mean that in relation to my example, it would be:

on intnx('month',a.cohort_mth,0,'b') = intnx('month',b.mth_end,0,'b')

This one!

Not this one:

Would something like this also work to answer my question?

on input(put(a.cohort_mth,yymmn6.),6.) = input(put(b.mth_end,yymmn6.),6.)

As I stated, formatting does not really help here, it is just extra work to format the result and then unformat it. Also, unless I am missing something here, this does not convert all the dates to Feb 1 2017 which would be needed to do the matching.

Re: Matching dates

Tom — Thu, 25 Jul 2019 15:33:07 GMT

You didn't completer remove the INPUT() function call.

on put(a.cohort_mth,yymmn6.) = put(b.mth_end,yymmn6.)
 and a.customer_ID = b.ID