topic Re: SAS Oracle where clause on date variable in SAS Programming

SAS Oracle where clause on date variable

ArseneWenger — Tue, 10 Jan 2017 23:06:47 GMT

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.

Re: SAS Oracle where clause on date variable

Reeza — Tue, 10 Jan 2017 23:15:44 GMT

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?

Re: SAS Oracle where clause on date variable

Reeza — Tue, 10 Jan 2017 23:16:16 GMT

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.

Re: SAS Oracle where clause on date variable

Patrick — Wed, 11 Jan 2017 01:51:13 GMT

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.