- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I am trying to submit this code
proc sql; connect to odbc as mycon (datasrc='xxxx' user='xxxx' password='xxxx' BULKLOAD=YES); create table Table01 AS select * from connection to mycon (select * from xxx.REQUEST where DT_TRAN_CREATED >= "&maxdate"dt); disconnect from mycon; quit;
&maxdate is defined as 28MAR2018:14:12:48.00.
When I run this code I get this error RROR: PROC SQL requires any created table to have at least 1 column.
Any ideas on a solution - Thanks in advance
|
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Your target database does not understand the SAS datetime literal; look at the DB documentation to see how datetime literals are expected to be.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Which database are you connecting to? Most databases don't allow double quotes for values.
Suryakiran
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I believe you have confused implicit and explicit passthru in your coding. Take a look at my MWSUG paper from a few years ago, https://www.lexjansen.com/mwsug/2014/SA/MWSUG-2014-SA03.pdf , and try implicit passthru for simpler coding. If you must use explicit passthru, make sure to list your desired columns in the database's own syntax, including datetime format for the database, not SAS DT! Good luck!
Nina L. Werner
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the paper, I found it online yesterday and found it useful. I am using explicit and not implicit because the tables I am querying have a lot of data and I only want a small section of that data.
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@DG1984 wrote:
Thanks for the paper, I found it online yesterday and found it useful. I am using explicit and not implicit because the tables I am querying have a lot of data and I only want a small section of that data.
Thanks
At least run a test of both methods to verify that explicit PT is needed. You might find that in many/most cases it's not necessary, if you're just doing simple subsets.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@DG1984 wrote:
Thanks for the paper, I found it online yesterday and found it useful. I am using explicit and not implicit because the tables I am querying have a lot of data and I only want a small section of that data.
Thanks
The SAS Access engine will try and push as much of the processing to the database as it manages.
Use the following set of SAS options at the beginning of your code. This will then show you in the log what actually gets executed on the database side and what on the SAS side.
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
For your code here there is a high chance the access engine will be able to fully convert your SQL to the database flavour and though all processing will happen in-database.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try %TSLIT
where DT_TRAN_CREATED >= %TSLIT(&maxdate));
Suryakiran
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Many thanks - although I get the error below
ERROR: CLI cursor fetch error: [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Change you &maxdate macro value to a format Oracle knows like 28-MAR-2018 14:12:48. and in your filter change the Oracle Datetime values to Characters using Oracle function TO_CHAR.
where TO_CHAR(DT_TRAN_CREATED,'DD-MON-YY HH24:MI:SS') >= %TSLIT(&maxdate));
Is there any specific reason for using explicit pass-through, instead of Implicit way (Using LIBNAME)
Suryakiran
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You need to convert your date string into something Oracle can understand. Below code worked for me in an actual project.
%let maxdate=28MAR2018:14:12:48.00;
%let maxdate_ora=to_date(%unquote(%str(%')&maxdate%str(%')),'DDMONYYYY:HH24:MI:SS');
You then can use above in one of below ways:
where DT_TRAN_CREATED >= &maxdate_ora
or..
where DT_TRAN_CREATED >= to_date(%unquote(%str(%')&maxdate%str(%')),'DDMONYYYY:HH24:MI:SS')