03-08-2013 10:42 AM
I have the following proc sql:
create table aaa as
from table1 a
left join table2 b
on a.id = b.id
datepart(b.datbegin) < a.datref < datepart(b.datend)
where datref is on table1 and it is a date9. and datbegin and datend are on table 2 and are datetime20
The query dose not work, I mean it does not return any row but it should.
Do there is something worng using datepart or managing datetime20 versus date9?
03-08-2013 11:02 AM
I would suggest first to make sure that datref is actually a date variable, and that begin and end are actually datetime variables. The format that they have doesn't have any impact on this. Manually retrieve a few of the rows from each table that should be joining, and check what the actual underlying numbers are. For example, today as a date variable is 19425, and as a datetime variable is anything between 1678320000 and 1678406399.
Here's a reference to the different types of variables:
Another question is whether a and b are SAS datasets or database tables. It would be worth checking if there might be a problem in how SAS is converting a DBMS date and/or datetime when it retrieves it.
03-08-2013 11:05 AM
First, try replacing your where condition with a.datref between datepart(b.datbegin) and datepart(b.datend)
see if that returns something.
03-08-2013 11:10 AM
Your code appears to work as intended if, like the other responders suggested, your data is in the expected form.
Your code though, as written, will exclude records that have a datbegin or datend on the datref date. Is that what you intended or did you want the < operators to actually be 'le'?