Hello,
I'm trying to build a query in the expression builder and it shows an error msg in the log, when I run the process for which i've written the query. If I run the same query in the Enhanced editor, it runs perfectly. Any help would greatly be appreciated.
The query goes like this in the expression builder:
"LIBNAME MMTGT ORACLE USER=MMTGT PASSWORD=XXXXX PATH='ORCL' PRESERVE_NAMES=YES;
LIBNAME MMSRC ORACLE USER=MMSRC PASSWORD=XXXXX PATH='ORCL' PRESERVE_NAMES=YES;
PROC SQL;
DROP TABLE MMTGT.CITY;
CREATE TABLE CITY (RENAME=(SUPP_CITY=CITY)) AS
(
SELECT DISTINCT(SUPP_CITY) FROM MMSRC.SUPPLIER_TBL
UNION
SELECT DISTINCT(TRANSPORTER_CITY) FROM
MMSRC.TRANSPORTER_TBL
UNION
SELECT DISTINCT(BANK_CITY) FROM MMSRC.BANK_TBL
);
QUIT;
=========================================================
The error msg I get in the log window goes like this:
LIBNAME MMTGT ORACLE USER=MMTGT PASSWORD=XXXXX PATH='ORCL'
PRESERVE_NAMES=YES;
NOTE: Libref MMTGT was successfully assigned as follows:
Engine: ORACLE
Physical Name: ORCL
LIBNAME MMSRC ORACLE USER=MMSRC PASSWORD=XXXXX PATH='ORCL'
PRESERVE_NAMES=YES;
NOTE: Libref MMSRC was successfully assigned as follows:
Engine: ORACLE
Physical Name: ORCL
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
cpu time 0.04 seconds
PROC SQL;
DROP TABLE MMTGT.CITY;
NOTE: Table MMTGT.CITY has been dropped.
CREATE TABLE CITY (RENAME=(SUPP_CITY=CITY)) AS
(
SELECT DISTINCT(SUPP_CITY) FROM MMSRC.SUPPLIER_TBL
UNION
SELECT DISTINCT(TRANSPORTER_CITY) FROM MMSRC.TRANSPORTER_TBL
UNION
SELECT DISTINCT(BANK_CITY) FROM MMSRC.BANK_TBL
);
NOTE: Table WORK.CITY created, with 7 rows and 1 columns.
QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.29 seconds
cpu time 0.07 seconds
NOTE: SCL source line.
AS SUPP_CITY length=20
--
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
"AS is UNDERLINED and is the ERROR"
