topic Re: Reading Oracle Date Issue in SAS Programming

Reading Oracle Date Issue

deleted_user — Fri, 16 Jul 2010 15:32:47 GMT

Hi,
I have a requirement to read oracle date. From the source table i need to filter the data on a Date basis. In the proc sql I need to write a where clause like 'compensationdate>='1-Mar-2010' and compensationdate<='31-Mar-2010' '.

Here I converted the date to SAS date and wrote the condition like

'compensationdate>='1-Mar-2010'd and compensationdate<='31-Mar-2010'd.

But I have a reuirement to decide the second date dynamically. For the purpose I used the intnx('month','1-Mar-2010'd,0,'E') which supposed to return a SAS date equivalent to '31-Mar-2010'd. It returns it correctly, the numeric equivalent of '31-Mar-2010'd.

But the condition doesnt work fine.
compensationdate>='1-Mar-2010'd and compensationdate<='31-Mar-2010'd is working fine. But compensationdate>='1-Mar-2010'd and compensationdate<=intnx('month','1-Mar-2010'd,0,'E') is not working fine.

Is not '31-Mar-2010'd equal to intnx('month','1-Mar-2010'd,0,'E') ???

Whate ever the function we are using which rerturns a SAS date coudn't be compared with an oracle date.

Please help me.

Thanks in advance.

Re: Reading Oracle Date Issue

Bill — Fri, 16 Jul 2010 16:08:19 GMT

It looks like you are confusing Oracle date syntax and SAS date syntax.

For the Oracle extract use where OracleDate>='01-mar-2010'. Once/If you have the data in a SAS dataset, then use SASDate >= '01mar2010'd

Re: Reading Oracle Date Issue

sbb — Fri, 16 Jul 2010 17:12:37 GMT

Suggested Google advanced search argument, this topic / post:

proc sql oracle date site:sas.com

Scott Barry
SBBWorks, Inc.

Re: Reading Oracle Date Issue

deleted_user — Sat, 17 Jul 2010 04:43:49 GMT

my requirement is to filter the data like compensationdate>='1-Mar-2010'd and compensationdate<='31-Mar-2010'd. It works fine if we write the condition like this. But when I use sas function which returns dates doesnt work here. Eg:compensationdate<=intnx('month','31-Mar-2010'd,0,'E'); No error but no data. Seems like '31-Mar-2010'd is not equal to intnx('month','31-Mar-2010'd,0,'E'). My source is oracle.

Re: Reading Oracle Date Issue

deleted_user — Sat, 17 Jul 2010 04:44:28 GMT

Re: Reading Oracle Date Issue

NN — Sat, 17 Jul 2010 09:19:44 GMT

You could try This TO_DATE(COMPENSATIONDATE)>= '01-Mar-10'
Might just work for u.

Re: Reading Oracle Date Issue

Cynthia_sas — Sat, 17 Jul 2010 14:58:51 GMT

Hi:
When you try to post code that contains < or <= or > or >=, the forum posting mechanism "thinks" you are trying to use an HTML tag and sometimes it will truncate your post while it looks for a valid HTML tag to go with the < and >.

So, when you post code, you either
1) have to edit the code in Notepad ahead of time to replace < with < and to replace > with > OR
2) you have to substitute LT for < and LE for <= and GT for > and GE for >=

There are some other nuggest of information on posting special characters and code to the forum here:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

cynthia