Where clause datetime format in Proc SQL SAS

[Dipu]

Hello,

I have to get the transaction between 13Sep2013 and 23Oct2013. Is this a correct syntax??

Where trans_date variable is in datetime format i.e. 23OCt2013:00:00:00

proc sql;

Select

Trans_date    from m1.transaction 

where  datepart (transaction_date) between '13Sep2013'd and 23Oct2013'd

quit;

please guide

thank you

[DBailey]

That should work as it truncates the time portion to the date.  You do have to be careful about using data from external databases that might not support the datepart function.  SAS can't pass that function through to the external database and so it would return all rows and perform the selection locally.

Are you having issues with the query as it is currently constructed?

[Dipu]

Thank you Dbailey,

first time when I ran it, it took time and but could not give any result in the log window the query was printed as it is w/o any note or error (actually it had a syntax error as Arthur mentioned).

Arthur thank you too, both trans_date and transaction_date are one and the same, it is just a typo error. I rectified it.

Will wait to see the query resolved. I was confused not sure for the code since transaction_date/trans_date is in datetime format.

Thank you

[art297 · Accepted solution]

You are using both trans_date and transaction_date .. are there really two different fields?

Also, you are missing a quote before the 2nd date and need a semi-colon before the quit statement.  i.e.,

proc sql;
  Select Trans_date
    from transaction
      where  datepart (trans_date) between '13Sep2013'd and '23Oct2013'd
  ;
quit;