BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
6 REPLIES 6
Bill
Quartz | Level 8
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggested Google advanced search argument, this topic / post:

proc sql oracle date site:sas.com


Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
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.
deleted_user
Not applicable
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.
NN
Quartz | Level 8 NN
Quartz | Level 8
You could try This TO_DATE(COMPENSATIONDATE)>= '01-Mar-10'
Might just work for u.
Cynthia_sas
SAS Super FREQ
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 &lt; and to replace > with &gt; 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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 943 views
  • 0 likes
  • 5 in conversation