topic Re: SQL Passthrough in SAS Programming

SQL Passthrough

Doug____ — Thu, 24 Sep 2020 21:42:55 GMT

In using the following code, an "error" box is returned each time that says :

Cannot open data set "XXX".

SQL View Work.XXX could not be processed because at least one of the data sets, or views reference directly (or indirectly) but it could not be located or opened successfully.

Now when I execute the code shown below, it runs fine and returns records, but if I want to keep it in a temp table using the #TABLENAME syntax, it returns the message shown above.

proc sql;

connect to odbc as AAA(datasrc="64bit AAAA" user="YYYYY" password="ZZZZZ");

create view XXX as

select * from connection to AAA

(select * from eav.vw_PRCF);

disconnect from AAA;

quit;

Basically I cannot create tables of any kind or the message above is returned.

Re: SQL Passthrough

CurtisMackWSIPP — Thu, 24 Sep 2020 21:47:15 GMT

I don't know what you mean by the " #TABLENAME syntax". If you replace the word "view" with "table" it will create a table. You will get an error if there is already a view with that name as SAS will not automatically delete a view.

Re: SQL Passthrough

Doug____ — Thu, 24 Sep 2020 22:15:40 GMT

When I issue the code below in pass through it returns the box that says it can't find it and no table is created.

create table #a

(id char(9),

ind_id int,

account_id int,

integration_id varchar(25),

employer_id int,

name varchar(100),

LAST_TRANSFER_DATE datetime,

NBR_TRANSFERS varchar(10),

SHORT_NAME varchar(100),

INVESTMENT_ACTIVE varchar(20),

OFFICER_CODE varchar(4000),

OFFICER_NAME varchar(200),

CLOSE_DATE Datetime,

ACCOUNT_ORIG varchar(4000),

DATE7_ENTERED varchar(25))

Re: SQL Passthrough

CurtisMackWSIPP — Thu, 24 Sep 2020 22:38:42 GMT

This is what I have in mind. Does it not work in your environment? I am not understanding the # you are using. It could be my ignorance however. I know that notation creates a temporary table in SQL Server. Is that what you are trying to do?

proc sql;

connect to odbc as AAA(datasrc="64bit AAAA" user="YYYYY" password="ZZZZZ");
create table Wanted as
select * from connection to AAA
(select * from eav.vw_PRCF);
disconnect from AAA;

quit;

Re: SQL Passthrough

SASKiwi — Thu, 24 Sep 2020 22:51:24 GMT

Please post your complete SAS log including the code and any notes and errors. Posting partial code and not the actual errors makes it hard to see what is actually going on.

Re: SQL Passthrough

ChrisNZ — Fri, 25 Sep 2020 00:10:53 GMT

@Doug____ wrote:

When I issue the code below in pass through it returns the box that says it can't find it and no table is created.

create table #a

(id char(9),

...

You can't create a table in SQL passthrough that starts with select.

For anything but select clauses, use execute by xxx () instead of select from connection to xxx () ;

Re: SQL Passthrough

Doug____ — Fri, 25 Sep 2020 19:36:26 GMT

Ok implemented some of this and consulted with the original writer and there were some SQL syntax issues to correct. Following that the code executes but but not completely. The log is shown below. Is this a SQL Server ODBC driver issue? What is with the odd characters?

DBC: ROLLBACK performed on connection 8.

Summary Statistics for ODBC are:
Total SQL execution seconds were: 61.562277
Total SQL describe seconds were: 0.000015
Total seconds used by the ODBC ACCESS engine were 61.563385

ERROR: CLI describe error:
䵛捩潲潳瑦孝䑏䍂䐠楲敶⁲㜱映牯匠䱑匠牥敶嵲卛䱑匠牥敶嵲湉慶楬⁤扯敪瑣渠浡⁥搧潢愮瑲捩
灩湡却慴畴味灹䱥潯畫❰.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
717 disconnect from wex;
NOTE: Statement not executed due to NOEXEC option.
718 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 1:01.82
cpu time 0.03 seconds

Re: SQL Passthrough

SASKiwi — Fri, 25 Sep 2020 23:14:13 GMT

Your SAS log does not contain any source (program) statements so is useless for diagnosing what is happening. If that is your complete log, then you need to add this at the start of your program, then rerun and re-post your log including source statements:

options source;

Re: SQL Passthrough

ChrisNZ — Sat, 26 Sep 2020 08:50:56 GMT

Don't specify the number of decimals in the format to read digits, unless you want to force the number of decimals.