BookmarkSubscribeRSS Feed
singhashupratap
Fluorite | Level 6

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 ;
 
3 REPLIES 3
LinusH
Tourmaline | Level 20

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
singhashupratap
Fluorite | Level 6
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.
LinusH
Tourmaline | Level 20
I think that there are more information in the log...?
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 3 replies
  • 3367 views
  • 0 likes
  • 2 in conversation