My organization uses SAS Enterprise Guide. The SAS system writes work tables to a Linux environment, and also has some permanent space within this environment we can write tables to. However, we write almost all final tables to an Oracle server.    We update our data hourly, but our tables are becoming so large (millions of rows, many GB) that write times for the final tables to Oracle are impeding our ability to update at this frequency. Using a direct SQL query can improve our write times dramatically ( from 50+ minutes for a data step or indirect SQL query to sub-5 minutes using direct query) when writing tables from one oracle location to another. However, to my knowledge, a direct oracle query can only be used if all datasets are within the oracle environment.    Is there a way to pull from tables outside of oracle in a direct SQL query?   Example below, where work is SAS work or other Linux directory:    PROC SQL NOPRINT; CONNECT TO ORACLE (PATH='ORACLE.PATH' USER='USERNAME' PASSWORD=PASSWORD); EXECUTE ( create table ORACLE.TEST_TABLE as select * from WORK.TEST_TABLE ) BY ORACLE; DISCONNECT FROM ORACLE; QUIT; ... View more

Hello,   I am having to pull any cannabis-related event based on ICD code from several datasets. The datasets have anywhere from 10-25 diagnosis variables where the ICD code could be found. I can code in every variable and ICD code manually, but would prefer to use an array if possible.    I have tried to code below, but it just ends up outputting entries that match the date range from the where clause and does not output based on the array section: DATA CANNABIS_DATA; SET DATA.HAVE where ((TRIAGE_DTTM >= '01JAN2017:00:00:00'd and TRIAGE_DTTM < '01FEB2017:00:00:00'd) or (VISIT_DTTM >= '01JAN2017:00:00:00'd and VISIT_DTTM < '01FEB2017:00:00:00'd)); ARRAY DXARRAY[10] DXCODE1 - DXCODE10; DO I = 1 TO 10; IF substr(DXARRAY[I],1,4) = 'T407' or substr(DXARRAY[I],1,3) = 'F12' THEN OUTPUT; END; RUn;    ... View more