02-06-2013 12:52 PM
I have a perplexing question.....I am connecting to a Sybase table using ODBC through SQL. The connection works fine...I am able to retrieve records from the table.
However, I need to restrict those records to certain date ranges. For instance all create dates after 01Jan2012. I have tried everything but cannot get this to work. When I open up the unfiltered table that retrieved using SQL, the field imported using SAS is a I get a date in DATE9. format, such as 27FEB2006. But I read in documentation that Sybase stores the dates as a timestamp format as 20060227.
I am so confused. I am wondering if I can use SAS functions such as the input function in my SQL code that connects to Sybase. Furthermore, I am wondering if I can even use a Date Literal like "26FEB2006"D in my SQL code at all. I honestly just don't know what I am doing here. Any help or if you could illuminate me based on your own experiences I would be most appreciative.
connect to ODBC (uid=xxxx pwd=xxxx dsn=xxxx);
create table tempdata as
select * from connection to odbc
disconnect from odbc;
02-06-2013 12:58 PM
The method your using is called SQL pass through and that means you need to use the Sybase functions to filter your date.
If you set up your connection outside the query using a libname statement for example, you could then use standard sas functions.
02-06-2013 01:15 PM
Just a quick point...
Setting up a library to access other database platforms works...but...If you use SAS intrinsic functions that do not translate into that platform's language, SAS will do all of the processing locally. So..you'll still get an answer...but the performance might be awful if you have joins across large tables. I think SAS date functions are not usually passed.
02-06-2013 01:35 PM
Depends on what 'date' functions, but I think date literals, which seems to be the issue here, would be passed. I don't see anything in the documentation to clarify that point.
02-06-2013 01:49 PM
Dates in sybase can be
You need to figure out which yours is and then use the appropriate type.
SyBooks Online Page 3
02-06-2013 02:50 PM
Thank you all for enlightening me! I feel very dumb....I understand now that I was using the pass-through process and that the functions etc that I was passing to Sybase were not intinsic Sybase functions. And that is why I was getting this error. This turned out to be incredibly easy. If I just add a clause onto the where statement like:
Create_date >= "2013/02/01"
then SQL returns exactly what I need. I was making this way too hard, not surprising since I didn't understand the mechanism behind how SAS and Sybase were communicating. Still not an expert by any means, but I think I get why it is working now and not before. Thanks so much for all your help!!!
02-07-2013 03:13 AM
If you are just sub setting according to a data constant, there is no need for writing explicit SQL pass-thru, the where clause will be passed to Sybase when using implicit pass-thru - and the syntax is simpler (and RDBMS independent).