I have a couple of techniques which may work for you. The first method assumes you can always update the complete record in your secondary table. So it deletes the records to be updated and then inserts the new record. In some cases this may be optimal. The second method uses Proc SQL update functionality and will give you the flexibility to update only a subset of columns if you wish. The code that I present below is more complicated than what might be needed with other versions of SQL. Some versions will allow you to write the update referencing the source table only once, but I found that I had to reference the source table once for each column being updated plus again for the update overall. I might be missing something here but I tried several variations before getting this to work. Note that if you can update the complete record, then you may want to benchmark both methods to see which performs best. Performance can be dependent upon the both SAS optimization and DB optimization. Let me know if this helps. Setup code: This section of code sets up some tables to mimic your tables. PROC SQL; * code to simulate primary1 and secondary1 tables ------------------------------------------------; create table work.primary1 (id char(4) primary key ,name char(1) ,code char(4) ,status char(1) ) ; insert into work.primary1 values('1639','F','TA1','1') values('1065','M','ME3','1') values('1400','M','ME1','1') values('1561','M',null,'1') values('1221','F','FA3','1') ; create table work.secondary1 as select * from work.primary1 ; quit ; First Method: Here I show the first method which is to delete records to be updated and then insert the new records. * two step process which deletes records from target table with keys for records with updated status. then insert changed records into table -----------------------------------------; proc sql ; * simulate a change in primary table ; update work.primary1 set code = 'abc' ,status = '0' where id = '1561' ; delete * from work.secondary1 where id in (select id from work.primary1 where status = '0' ) ; insert into work.secondary1 select id ,name ,code ,status from work.primary1 where status = '0' ; quit ; Second Method: Here, I use explicit updating. * one step process for updating -----------------------------; proc sql ; * simulate a change in primary table ; update work.primary1 set code = 'xyz' ,status = '0' where id = '1561' ; * This is the real update here. **************************; update work.secondary1 t1 set name = ( select name from work.primary1 t2 where t2.status = '0' ) ,code = ( select code from work.primary1 t3 where t1.id = t3.id and t3.status = '0' ) ,status = ( select status from work.primary1 t4 where t1.id = t4.id and t4.status = '0' ) where t1.id = (select id from work.primary1 where status = '0' ) ; QUIT;
... View more