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!
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.