Conversion between datetime20. and date9.

Reply
Contributor
Posts: 58

Conversion between datetime20. and date9.

Hi,

I have the following proc sql:

       proc sql;
          create table aaa as
             select

                 a.id,

                 b.datbegin,

                 b.datend
             from table1 a
             left join table2 b
             on a.id = b.id
             where
                datepart(b.datbegin) < a.datref < datepart(b.datend)

       ;
       quit;


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?

Many thanks,

Regards

PROC Star
Posts: 1,167

Re: Conversion between datetime20. and date9.

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:

http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p1wj0wt2ebe2a0n1lv4l...

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.

Respected Advisor
Posts: 4,932

Re: Conversion between datetime20. and date9.

First, try replacing your where condition with a.datref between datepart(b.datbegin) and datepart(b.datend)
see if that returns something.

PG

PG
PROC Star
Posts: 7,492

Re: Conversion between datetime20. and date9.

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'?

Ask a Question
Discussion stats
  • 3 replies
  • 282 views
  • 0 likes
  • 4 in conversation