Desktop productivity for business analysts and programmers

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

Reply
Occasional Contributor
Posts: 9

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

[ Edited ]

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 ;
 
Super User
Posts: 5,391

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

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.

Data never sleeps
Occasional Contributor
Posts: 9

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

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.
Super User
Posts: 5,391

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

I think that there are more information in the log...?
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 730 views
  • 0 likes
  • 2 in conversation