title: How to update an Oracle Table from SAS efficiently?
body: <p><strong>The problem I am trying to solve:</strong></p>
  
  <p>I have a SAS dataset <code>work.testData</code> (in the work library) that contains 8 columns and around 1 million rows. All columns are in text (i.e. no numeric data). This SAS dataset is around 100 MB in file size. My objective is to have a step to parse this entire SAS dataset into Oracle. i.e. sort of like a "copy and paste" of the SAS dataset from the SAS platform to the Oracle platform. The rationale behind this is that on a daily basis, this table in Oracle gets "replaced" by the one in SAS which will enable downstream Oracle processes. </p>
  
  <p><strong>My approach to solve the problem:</strong></p>
  
  <p><em>One-off initial setup in Oracle:</em></p>
  
  <ol>
  <li>In Oracle, I created a table called <code>testData</code> with a table structure pretty much identical to the SAS dataset <code>testData</code>. (i.e. Same table name, same number of columns, same column names, etc.).</li>
  </ol>
  
  <p><em>On-going repeating process:</em></p>
  
  <ol start="2">
  <li>In SAS, do a SQL-pass through to truncate ora.testData (i.e. remove all rows whilst keeping the table structure). This ensure the ora.testData is empty before inserting from SAS.</li>
  <li>In SAS, a LIBNAME statement to assign the Oracle database as a SAS library (called ora). So I can "see" what's in Oracle and perform read/update from SAS.</li>
  <li>In SAS, a PROC SQL procedure to "insert" data from the SAS dataset work.testData into the Oracle table ora.testData.</li>
  </ol>
  
  <p><strong>Sample codes</strong></p>
  
  <p><em>One-off initial setup in Oracle:</em></p>
  
  <p>Step 1: Run this Oracle SQL Script in Oracle SQL Developer (to create table structure for table testData. 0 rows of data to begin with.)</p>
  
   <pre class="lang-sql prettyprint-override"><code>DROP TABLE testData;
  CREATE TABLE testData
    (
      NODENAME          VARCHAR2(64) NOT NULL,
      STORAGE_NAME      VARCHAR2(100) NOT NULL,
      TS                VARCHAR2(10) NOT NULL,
      STORAGE_TYPE      VARCHAR2(12) NOT NULL,
      CAPACITY_MB       VARCHAR2(11) NOT NULL,
      MAX_UTIL_PCT      VARCHAR2(12) NOT NULL,
      AVG_UTIL_PCT      VARCHAR2(12) NOT NULL,
      JOBRUN_START_TIME VARCHAR2(19) NOT NULL
    )
  ;
  COMMIT;
  </code></pre>
  
  <p><em>On-going repeating process:</em></p>
  
  <p>Step 2, 3 and 4: Run this SAS code in SAS</p>
  
   <pre class="lang-sas prettyprint-override"><code>******************************************************;
  ******* On-going repeatable process starts here ******;
  ******************************************************;
  
  *** Step 2: Trancate the temporary Oracle transaction dataset;
  proc sql;
    connect to oracle (user=XXX password=YYY path=ZZZ);
    execute (
      truncate table testData
    ) by oracle;
    execute (
      commit
    ) by oracle;
    disconnect from oracle;
  quit;
  
  *** Step 3: Assign Oracle DB as a libname;
  LIBNAME ora Oracle user=XXX password=YYY path=ZZZ dbcommit=100000;
  
  *** Step 4: Insert data from SAS to Oracle;
  PROC SQL; 
    insert into ora.testData
    select NODENAME length=64,
           STORAGE_NAME length=100,
           TS length=10,
           STORAGE_TYPE length=12,
           CAPACITY_MB length=11,
           MAX_UTIL_PCT length=12,
           AVG_UTIL_PCT length=12,
           JOBRUN_START_TIME length=19
    from work.testData; 
  QUIT;
  
  ******************************************************;
  **** On-going repeatable process ends here       *****;
  ******************************************************;
  </code></pre>
  
  <p><strong>The limitation / problem to my approach:</strong></p>
  
  <p>The Proc SQL step (that transfer 100 MB of data from SAS to Oracle) takes around 5 hours to perform - the job takes too long to run!</p>
  
  <p><strong>The Question:</strong></p>
  
  <p>Is there a more sensible way to perform data transfer from SAS to Oracle? (i.e. updating an Oracle table from SAS). </p>