BookmarkSubscribeRSS Feed
Anotherdream
Quartz | Level 8

Hello everyone. I have an odbc connection set up to a sql server database (example shown below). I need to pull in data from this table specified on a specific Quarter.

libname green odbc dsn='odbc_mydsn' schema=dbo bulkload=yes;

Normally I would do.

data answer;

set Green.MYtable;

where qtr(datepart(DATEFIELD))=4;

run;

The issue Is I get the error:

ERROR: A database error occurred. The database specific error follows:

       DATABASE error: The function QTRis not supported by the database ODBC


I can specify this using an IF statement, the issue is the table is VERY large, and the difference between an If statement and a where statement is far too large.

Example, a simple "where column=X" if statement runs for 3 minutes, while in a where clause it runs in 2 seconds (the column is indexed).

Does anyone know a way to pass the qtr function into a sql server database, or do I have to settle using an If statement? I am thinking of using an odbc passthrough query and passing macro variables into it.


Please note the field I am trying to pull from is a DATETIME, and I have already tried "SQL_FUNCTIONS=ALL", with the qtf(datefield)=4 and it does not work.

If anyone has any suggestions all are welcome. Thank you!

Brandon

1 REPLY 1
Anotherdream
Quartz | Level 8

Please cancel this post. I am just going to do it with a proc sql step instead of a datastep...

I would like to know why the proc sql step works and the data step does not, even through they both use the odbc connection string. If anyone can explain this to me I will credit it with correct answer. AKA why does this work.....

libname green odbc dsn='odbc_mydsn' schema=dbo bulkload=yes;

proc sql;

create table testybaby as

select *

from Green.Loanlevelpopulation

where year(datepart(datamonth))=2013

and qtr(datepart(datamonth))=4;

run;

But this doesn't.

data testybaby;

set green.Loanlevelpopulation;

where year(datepart(datamonth))=2013

and qtr(datepart(datamonth))=4;

run;

Thanks all!

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
  • 1 reply
  • 697 views
  • 0 likes
  • 1 in conversation