Help using Base SAS procedures

proc sql to oracle specific date

Accepted Solution Solved
Reply
Contributor ivs
Contributor
Posts: 26
Accepted Solution

proc sql to oracle specific date

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!


Accepted Solutions
Solution
‎10-20-2014 04:56 PM
Super User
Posts: 5,256

Re: proc sql to oracle specific date

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).

Data never sleeps

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: proc sql to oracle specific date

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;

Contributor ivs
Contributor
Posts: 26

Re: proc sql to oracle specific date

to stat@sas:

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

to LinusH:
thanks! gonna try it.

Solution
‎10-20-2014 04:56 PM
Super User
Posts: 5,256

Re: proc sql to oracle specific date

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).

Data never sleeps
Super Contributor
Posts: 578

Re: proc sql to oracle specific date

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.

Contributor ivs
Contributor
Posts: 26

Re: proc sql to oracle specific date

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1716 views
  • 0 likes
  • 4 in conversation