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 ;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.