/* Example of how to set up a native Oracle query. The inner Oracle subquery is executed on the Oracle database and is written in Oracle PL/SQL dialect. The outer SAS query is executed in the local SAS environment and facilitates data tranfer from Oracle to SAS. */ %let OUTTBL=WORK.CURJOB; proc sql; /* Open ephemeral EPM connection. Notice how the userid and password as set in the connection script. */ connect to oracle (user=&epmuser password=&epmpass path='EPM'); /* Start SAS outer query - This creates a data set called CURJOB in the SAS WORK library */ create table &OUTTBL as select * from connection to oracle ( /* Start of Oracle subquery. Notice that the SYSADM schema needs to be explicitly referenced. */ select distinct EMPLID ,NAME ,DEPTID from SYSADM.PS_UW_HR_CURJOB_VW where BUSINESS_UNIT='UWMSN' and HR_STATUS='A' /* End of Oracle subquery */ ) /* This order by is done by SAS */ order by EMPLID ; /* End SAS outer query */ /* Close ephemeral EPM connection */ disconnect from oracle; quit;