topic Re: proc sql to oracle specific date in SAS Procedures

proc sql to oracle specific date

ivs — Mon, 20 Oct 2014 20:04:45 GMT

hi all,

can someone please help!

I can't figure out how to pull data for a specific date.

I am trying to query oracle database.

proc sql noerrorstop;

connect to oracle (user='XX' password='XX' buffsize=100 path='XX');

create table work.CUST as

select DATA_DT, EMPL_ID, CUST_NBR, Count(CUST_NBR)

from connection to oracle (select * from MYTABLE)

where DATA_DT = dmy(01, 01, 2013) and EMPL_ID in (select * from work.empls);

group by 1, 2, 3, 4;

disconnect from oracle;

quit;

i get error for the above:

ERROR: The original SQL statement contains a UDF that is not being passed down to the database.

SQL execution is stopped.

ERROR: Expression using equals (=) has components that are of different data types.

many thanks in advance!

Re: proc sql to oracle specific date

stat_sas — Mon, 20 Oct 2014 20:39:57 GMT

Try this.

proc sql noerrorstop;

connect to oracle (user='XX' password='XX' buffsize=100 path='XX');

create table work.CUST as

select DATA_DT, EMPL_ID, CUST_NBR, Count(CUST_NBR)

from connection to oracle (select * from MYTABLE)

where DATA_DT = '01JAN2013'd and EMPL_ID in (select EMPL_ID from work.empls)

group by 1, 2, 3, 4;

disconnect from oracle;

quit;

Re: proc sql to oracle specific date

LinusH — Mon, 20 Oct 2014 20:56:36 GMT

Don't use explicit pass-thru.

In your case, SAS handles the date conversion for you AND pushes part of the where clause to the RDBMS if you use implicit pass-thru (that is, query a Oracle libname instead).

Re: proc sql to oracle specific date

ivs — Mon, 20 Oct 2014 21:08:28 GMT

to stat@sas:

this format gets no error messages, but the query hangs up with no results.

to LinusH:
thanks! gonna try it.

Re: proc sql to oracle specific date

DBailey — Tue, 21 Oct 2014 12:31:08 GMT

You're only selecting three variables to group by but your grouping by 4. Not sure if that's by design.

One other issue with oracle is that I believe SAS considers most oracle date columns (at least all of ours) to be datetime, not date...so you would have to use '01Jan2013:0:0'dt.

Re: proc sql to oracle specific date

ivs — Tue, 21 Oct 2014 17:54:33 GMT

to LinusH: The libname approach worked! Thank you!

to DBailey: its just a minor typo there - thanks for pointing this out. LinusH's answer resolved the fundamental problem.