DATA Step, Macro, Functions and more

SAS Oracle where clause on date variable

Reply
Occasional Contributor
Posts: 13

SAS Oracle where clause on date variable

Hi,

 

I am trying to subset oracle dataset on date variable which is numeric and has sas format datetime27.6. for eg. it looks like: 01JAN1960:13:01:01.000000

attached is what i have my code currently..

 

I am getting error with following query. Please advice correct way of using datetime subset.

 

Thanks in advance.


Capture.PNG
Super User
Posts: 19,815

Re: SAS Oracle where clause on date variable

Posted in reply to ArseneWenger

It has to be valid SQL syntax for your server, which it isn't.

 

Replace the macro variable with the value to see what you need to do, possibly include quotes?

Super User
Posts: 19,815

Re: SAS Oracle where clause on date variable

EDIT: I'd post the modification to show but I don't want to type out your code. Post as text in the future please.

Respected Advisor
Posts: 4,173

Re: SAS Oracle where clause on date variable

[ Edited ]
Posted in reply to ArseneWenger

Your macro variable &my_date must resolve to something like: '11JAN2017:12:34:57'

 

Looking at picture you've posted, I believe you're missing the single quotes around the date string and the column between yyyy and HH24.

 

Oh... and reading your initial post again, I also come to realize that you're applying a SAS DateTime format on a SAS Date value. That's why you end up with a year of 1960.

 

I am trying to subset oracle dataset on date variable which is numeric and has sas format datetime27.6. for eg. it looks like: 01JAN1960:13:01:01.000000

 

 

On the SAS side: Use format DATE9.  This will give you a string like 11JAN2017

 

On the Oracle side use something like:

where trunc(e.timestamp,'DATE') > to_date(&my_date,'ddmonyyyy')  

....don't forget to wrap the single quotes around the date string value!

 

https://docs.oracle.com/cd/E29805_01/server.230/es_eql/src/cdfp_analytics_lang_trunc.html

 

And last but not least:

When using SQL pass-through then I'd always first use a native database client to develop the code (i.e. SQL Developer). Only when the code works copy paste it into a SAS pass-through block and add your customizations like SAS macro variables.

 

 

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