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!
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).
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;
to stat@sas:
this format gets no error messages, but the query hangs up with no results.
to LinusH:
thanks! gonna try it.
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).
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.