Hi @SASsy05
I didn't use your exact table names (I should have) but I did create what I think is a working example. If your tables are very large it may help to use explicit pass-through for the UPDATE statement. The example below uses only SAS (no PostgreSQL). The EXISTS predicate is important and stops a very sneaky side effect. It may help to remove it and see what happens. Here you go.
proc sql;
create table drug_exposure (drug_exposure_id int,
drug_concept_id int);
insert into drug_exposure values (1,10);
insert into drug_exposure values (2,20);
insert into drug_exposure values (3,30);
insert into drug_exposure values (5,50);
create table drug_txtmatch (drug_exposure_id int,
drug_cid int);
insert into drug_txtmatch values (1,11);
insert into drug_txtmatch values (2,22);
insert into drug_txtmatch values (3,33);
insert into drug_txtmatch values (4,44);
quit;
proc sql;
update drug_exposure as t1
set drug_concept_id = (select t2.drug_cid
From drug_txtmatch as t2
Where t1.drug_exposure_id=t2.drug_exposure_id)
where exists (select 1
from drug_txtmatch as t3
where t1.drug_exposure_id = t3.drug_exposure_id);
quit;
Hope this helps.
Best wishes, Jeff
... View more
