topic Re: Where clause datetime format in Proc SQL SAS in SAS Programming

Where clause datetime format in Proc SQL SAS

Dipu — Fri, 27 Dec 2013 16:47:49 GMT

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

Re: Where clause datetime format in Proc SQL SAS

DBailey — Fri, 27 Dec 2013 17:08:50 GMT

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?

Re: Where clause datetime format in Proc SQL SAS

art297 — Fri, 28 Oct 2016 13:57:45 GMT

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;

Re: Where clause datetime format in Proc SQL SAS

Dipu — Fri, 27 Dec 2013 17:22:37 GMT

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