BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ivs
Calcite | Level 5 ivs
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

5 REPLIES 5
stat_sas
Ammonite | Level 13

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;

ivs
Calcite | Level 5 ivs
Calcite | Level 5

to stat@sas:

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

to LinusH:
thanks! gonna try it.

LinusH
Tourmaline | Level 20

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
DBailey
Lapis Lazuli | Level 10

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.

ivs
Calcite | Level 5 ivs
Calcite | Level 5

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. 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 5219 views
  • 0 likes
  • 4 in conversation