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
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 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;
Thank you, I am still getting an error.
ERROR: ORACLE execute error: ORA-00905: missing keyword.
I'm not that familiar with Oracle, but isn't this SAS code, not Oracle code?
t1.ENCTR_STRT_DT BETWEEN '1Jan2016:0:0:0'dt AND '31Mar2017:0:0:0'dt
It has to be Oracle SQL and date formats.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.