Hi All,
I am trying to figure out how I can create temporary tables/volatile tables in SAS - ORACLE pass-thru sql.
Below is my code, but it is throwing an error saying
ERROR: ORACLE execute error: ORA-00933: SQL command not properly ended.
PROC SQL;
CONNECT TO ORACLE (PATH="CS.WORLD" USER="&uid_db2." PASSWORD="&pwd_db2.");
EXECUTE (
CREATE GLOBAL TEMPORARY TABLE QUERY_FOR_INSTN_ENCTR as
select * from connection to oracle
(
SELECT t1.RGN_CD,
t1.ENCTR_SK,
t1.ENCTR_SRC_SYS_CD,
t1.CORR_MRN_PLS_NB,
t1.LOC_IK,
t1.CARE_SETNG_IK,
t1.PTNT_RSDNC_ZIP_IK AS PTNT_RSDNC_ZIP_IK1,
t1.ENCTR_STRT_TS,
t1.ENCTR_END_TS,
t1.DRG_IK,
t1.PTNT_RSDNC_ZIP_IK,
t1.PRNCPL_ICD_DIAG_IK,
t1.PRMY_ATND_PRVDR_IK,
t1.MDC_IK,
t1.ADMT_PRVDR_IK,
t1.PRNCPL_ICD_PROC_IK,
t1.PRNCPL_PROC_PRVDR_IK,
t1.PTNT_AGE_YR_NB,
t1.PTNT_BRTH_DT,
t1.PTNT_GNDR_CD,
t1.INPAT_LOS_DAY_CT,
t1.TTL_LOS_DAY_CT
FROM IA_V.INSTN_ENCTR t1
WHERE t1.RGN_CD = '08' AND t1.ENCTR_STRT_DT BETWEEN '1Jan2016:0:0:0'dt AND '31Mar2017:0:0:0'dt AND
t1.ENCTR_SRC_SYS_CD = 'C' AND t1.PTNT_AGE_YR_NB < 18
) on commit preserve rows) by oracle;
Create table STEP150 as
SELECT * FROM CONNECTION TO ORACLE
(
Select *
from QUERY_FOR_INSTN_ENCTR
);
disconnect from oracle;
QUIT;
I am not sure my code/syntax is correct. Please advice.
Thanks
... View more
