update :
TableA
Id Value
1 abc
2 pqr
TableB
Id Value
1
2 pqr
how to update tableb for an id if value is missing, update with valur from tableA?
Final output:
TableB
Id Value
1 abc
2 pqr
data a;
input id value $;
cards;
1 abc
2 pqr
;
data b;
input id value $;
cards;
1 .
2 pqr
;
data b;
modify b a;
by id;
if _IORC_=0 then replace;
else _ERROR_=0;
run;
if there are multiple columns and only one column is to be updated, like below:
I dont want score to be replaced. only value ,the below logic doesnt work,right?
data a;
input id value $ score;
cards;
1 abc 350
2 pqr 275
;
data b;
input id value $3-6 score ;
cards;
1 371
2 pqr 372
;
run;
data b;
modify b a;
by id;
if _IORC_=0 then replace;
else _ERROR_=0;
run;
@SASPhile please post a better sample dataset that represents your needs. You are piece mealing your request. Don't play the manager role in requesting results please.
data a;
input id value $ score;
cards;
1 abc 350
2 pqr 275
;
data b;
input id value $ score ;
cards;
1 . 371
2 pqr 372
;
proc sql;
update b
SET value = (SELECT value
FROM a
WHERE b.id = a.id)
WHERE value=' ';
quit;
And as a matter of fact, you can push the proc sql update query in-database and have it done at that level rather than at SAS level.
Assuming you are just updating value sourcing from Table A, you could use dataset option to keep id and value alone
data b;
modify b a(keep=id value);/*drop the rest*/
by id;
if _IORC_=0 then replace;
else _ERROR_=0;
run;
If TableB never has multiple observations for the same ID, you could use:
data want;
update b (in=keepme) a;
by id;
if keepme;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.