Where clause datetime format in Proc SQL SAS

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Where clause datetime format in Proc SQL SAS

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


Accepted Solutions
Solution
‎10-28-2016 09:58 AM
PROC Star
Posts: 7,363

Re: Where clause datetime format in Proc SQL SAS

[ Edited ]

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;

 

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: Where clause datetime format in Proc SQL SAS

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?

Contributor
Posts: 43

Re: Where clause datetime format in Proc SQL SAS

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

Solution
‎10-28-2016 09:58 AM
PROC Star
Posts: 7,363

Re: Where clause datetime format in Proc SQL SAS

[ Edited ]

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;

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 23591 views
  • 7 likes
  • 3 in conversation