BookmarkSubscribeRSS Feed
Doug____
Pyrite | Level 9

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.

 

8 REPLIES 8
CurtisMackWSIPP
Lapis Lazuli | Level 10

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. 

Doug____
Pyrite | Level 9

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

 

 

SASKiwi
PROC Star

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.

ChrisNZ
Tourmaline | Level 20

@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 () ;

CurtisMackWSIPP
Lapis Lazuli | Level 10

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;
Doug____
Pyrite | Level 9

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

SASKiwi
PROC Star

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;
ChrisNZ
Tourmaline | Level 20

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1019 views
  • 2 likes
  • 4 in conversation