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;
