topic How to create a volatile table in oracle pass-thru SAS SQL code? in SAS Programming

How to create a volatile table in oracle pass-thru SAS SQL code?

renjithr — Thu, 25 May 2017 20:11:13 GMT

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

Re: How to create a volatile table in oracle pass-thru SAS SQL code?

SASKiwi — Thu, 25 May 2017 21:39:59 GMT

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;

Re: How to create a volatile table in oracle pass-thru SAS SQL code?

renjithr — Thu, 25 May 2017 22:00:57 GMT

Thank you, I am still getting an error.

ERROR: ORACLE execute error: ORA-00905: missing keyword.

Re: How to create a volatile table in oracle pass-thru SAS SQL code?

Reeza — Fri, 26 May 2017 00:11:59 GMT

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.