Full Join using proc sql but not working

[ngrannum75]

Hi, I am trying to perform a full join using two tables, there is no output on the log; my SAS server just seems to be timing out. Not sure what is happening or how to troubleshoot this issue. Here is my code.

%LET start_date = 01JAN20:12:00: 00.000000;
proc sql;
create table uat as
select x.sku_id, x.Base_Price_Amt, xx.On_Air_Qty,
y.Shipping_And_Handling_Amt, y.Source_Cd
from hist x full join expos y on (x.sku_id = y.sku_id)
where x.Valid_Start_Dttm between "&start_date."d and today() - 1
;
quit;

[Kurt_Bremser]

You have an incorrectly written datetime value which you try to use as a date literal.

[ngrannum75]

Thank you. 

I am new to SAS EG. How do you suggest I correct this variable? In the table uses this datetime format and i would like to filter for a specific date range. is my macro incorrect or my where clause? 

Valid_Start_Dttm 

"01JAN2020:12:00:00.000000"d

%LET start_date = 01JAN2020:12:00:00.000000;

where x.Valid_Start_Dttm BETWEEN "&start_date."d AND TODAY()- 1;

I am guessing I would need a datetime in my where clause? 

Thank you. 

[Tom · Accepted solution]

Dates are stored as the number of DAYS since 1960. Datetimes are stored as the number of SECONDS since 1960.  So unless your DATETIME values are very early in the date 01JAN1960 they will never be between any two DATE values.

Use a DATETIME literal by appending DT instead of just D to the quoted string that the DATETIME informat can interpret.  Make sure to use a DATETIME value for the upper bound of your interval also.

where x.Valid_Start_Dttm BETWEEN "&start_date."dt AND intnx('dtday',datetime(),-1)

Or since it appears you don't really care about the time of day part of the datetime variable, convert it from seconds to days.

where datepart(x.Valid_Start_Dttm) BETWEEN datepart("&start_date."dt) AND today()-1

On source of confusion is that SAS is not going to throw an error when you give it extra characters in your date literal.  So SAS will happily treat these two things as the same number of days without any error or warning.

"01JAN2020:12:00:00.000000"d
"01JAN2020"d

[ngrannum75]

Thank you so much! This clears up my confusion!