title: SAS - looping through a source table and inserting into destination tables
body: <p>I was wondering if anyone can help with a sas problem i have.</p>
  
  <p>I have to go over 10000 rows in an existing source table and insert the source table values into multiple destination tables.  I have to do the inserts one row at at time from the source table and also make sure to insert the primary key from the parent table(during insert) into the child tables (during insert)</p>
  
  <p>I am using SAS API (SAS EG)</p>
  
  <p>Here is what the <strong>source table</strong> looks like (please note this is another table not a data proc), the name of this table is countrycodelist which has the following columns for example:</p>
  
  <pre>
  Txn_id  statuscode  Typecode  classcode  identifier 
  
  001          2          cnt      p1         13485
  002          2          na       n1         13444
  003          4          cnt      n1         12888
  </pre>
  
  <p>Right now i have the following code for the destination table:
  NOTE: destination tables are already created and i have to just insert into them (tableA/tableB)</p>
  
  <pre><code>PROC SQL;
  Insert into tableA (TXNID, STATUSCODE,CREATET,LASTUPDATET)
  Select TXN_ID,STATUSCODE, datetime(), datetime()
  From CountryCodeList
  ;
  </code></pre>
  
  <p>Quit;</p>
  
  <p>After the above code has run status code is inserted into the table sim.tableA but this table has a key (pk) that is autoincrement. I need to use this key to insert into the child table so that a proper connection is made from the parent table to the child table below. </p>
  
  <pre><code>PROC SQL;
  Insert into tableB (tableAID,TypeCode,ClassCode,DisplayID,)
  Select 1101,TypeCode,ClassCode,Identifier
  From CountryCodelist;
  QUIT;
  </code></pre>
  
  <p>I manually inserted the last PK# from the tableA (1101) this works for small table inserts, but in large scale inserts i would need to grab the last PK from tableA and insert into tableB. SQL has functions such as scope_identity() or usage of @@IDENTITY to get last inserted key but these don't work in SAS or maybe I am going about it the wrong way. </p>
  
  <p>Also i need to process these inserts one row at a time from the source table (CountryCodelist). This countrycodelist table can be updated over time and could grow past 10k records</p>