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.
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?
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.