topic ERROR: File WORK.tablename.DATA does not exist. in SAS Enterprise Guide

ERROR: File WORK.tablename.DATA does not exist.

singhashupratap — Tue, 10 May 2016 11:14:23 GMT

I'm trying to execte simple proc-sql but it give an error that

ERROR: File WORK.HOTELS_HOTELCANCELLATION.DATA does not exist. HOTELS_HOTELCANCELLATION is table name


proc sql ; 
connect to odbc (dsn=inventory user=goibibo password=''); 
SELECT id, cancelid, bookingobj_id, bookingamount, refundedamount, cancellationcharges, nettbookingamount, 
nettrefundedamount, nettcancellationcharges, 
(CASE WHEN confirmationflag = true THEN 'Confirmation flag is true' WHEN confirmationflag = false THEN 'Confirmation flag is false' END) as confirmationflag, 
(CASE WHEN payathotelflag = true THEN 'PAH flag is true' WHEN payathotelflag = false THEN 'PAH flag is false' END) as payathotelflag, 
(CASE WHEN reconfirmationflag = true THEN 'Reconfirmation flag is true' WHEN reconfirmationflag = false THEN 'Reconfirmationflag flag is true' END) as reconfirmationflag, 
hotelstatus, vendorstatus, createdon, modifiedon 
FROM hotels_hotelcancellation WHERE createdon BETWEEN '2016-05-01 00:00:00' AND '2016-05-08 23:59:95'; 
disconnect from odbc; 
quit ;

Re: ERROR: File WORK.tablename.DATA does not exist.

LinusH — Tue, 10 May 2016 11:38:09 GMT

You are mixing the concepts of implicit and explicit SQL pass thru.

The CONNECT statement prepares you to do explicit SQL pass thru.

But you need an outer select statement that calls your SQL which encapsulated:

select *

from connection to odbc (your SQL goes here...);

An alternative is to use a libname to your ODBC data source, then you can skip all this "connect to" and "from connection to". But then you need to use SAS/ANSI SQL instead...

See online doc for SAS/ACCESS for syntax and examples.

Re: ERROR: File WORK.tablename.DATA does not exist.

singhashupratap — Tue, 10 May 2016 12:37:54 GMT

I do it this way

libname inibibo '/sasdata/sasdemo1/ingoibibo';

proc sql EXEC;
connect to odbc (dsn=inventory user=goibibo password='');
select * from connection to odbc
(SELECT id, cancelid, bookingobj_id, bookingamount, refundedamount, cancellationcharges, nettbookingamount,
nettrefundedamount, nettcancellationcharges,
(CASE WHEN confirmationflag = true THEN 'Confirmation flag is true' WHEN confirmationflag = false THEN 'Confirmation flag is false' END) as confirmationflag,
(CASE WHEN payathotelflag = true THEN 'PAH flag is true' WHEN payathotelflag = false THEN 'PAH flag is false' END) as payathotelflag,
(CASE WHEN reconfirmationflag = true THEN 'Reconfirmation flag is true' WHEN reconfirmationflag = false THEN 'Reconfirmationflag flag is true' END) as reconfirmationflag,
hotelstatus, vendorstatus, createdon, modifiedon
FROM HOTELS_HOTELCANCELLATION WHERE createdon BETWEEN '2016-05-01 00:00:00' AND '2016-05-08 23:59:95');
disconnect from odbc;
quit ;

getting error that NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
35 disconnect from odbc;
NOTE: Statement not executed due to NOEXEC option.

Re: ERROR: File WORK.tablename.DATA does not exist.

LinusH — Wed, 11 May 2016 03:47:40 GMT

I think that there are more information in the log...?