topic How to left join two tables using dates(informat YYMMDD10. & ANYDTDTM40.) in SAS Enterprise Guide

How to left join two tables using dates(informat YYMMDD10. & ANYDTDTM40.)

x2PSx — Fri, 15 Jul 2022 18:20:47 GMT

Hello,

Using SAS EG 8.3 and I'm trying to do a left join between two tables using dates and a proc sql.

One table has a date variable with these properties:

Type: Numeric

Group: Date

Format: YYMMDD10.

Informat: YYMMDD10.

Values are displayed like this: 2023-09-15

The other table has a date variable with these properties:

Type: Numeric

Group: Date

Format: DATETIME.

Informat: ANYDTDTM40.

Values are displayed like this: 15Sep2023 0:00:00

Could someone help me with the transformation I have to do to make this join happen?

Thank you very much!

ballardw — Fri, 15 Jul 2022 18:50:30 GMT

It would have been nice to have detail like variable names.

You want to use the DATEPART function on the Datetime variable to pull the date for comparison with any date value

So and ON clause for a joint might look like:

on a.datevariable = datepart(b.datetimevariable)

if you want to match a specific time then you would add the time portion to the date which is easy with the DHMS function:

on dhms(a.datevariable,12,15,0) = b.datetimevariable

for example would match on the date plus when hour=12 and minutes=15 and seconds=0 in both variables.

x2PSx — Fri, 15 Jul 2022 20:58:43 GMT

The datepart function did the trick, thank you for your time ballardw!