topic Re: Update values in a table if columns match a second table? in SAS Procedures

Update values in a table if columns match a second table?

Datino — Wed, 15 Mar 2017 01:02:49 GMT

Hello,

I'm trying to update the names table, but only if two of its columns match the ones from the donations table. Is it possible to do it in a single block of code?

data names;
input name $ ssn $ invited $;
datalines;
john 10 n
phil 12 n
charles 13 n
;
data donations;
input name $ ssn $ donated;
datalines;
joe 23 1000
george 18 4599
phil 12 6553
;

proc sql;
	update names as a
	set invited='y'
	where (a.name=donations.name and a.ssn=donations.ssn);
quit;

Re: Update values in a table if columns match a second table?

Jagadishkatam — Wed, 15 Mar 2017 01:12:18 GMT

Please try

proc sql;
create table test as select a.name,a.ssn,b.donated, 
case when a.name=b.name and a.ssn=b.ssn then 'y' else a._invited end as invited 
from names(rename=(invited=_invited)) as a left join donations as b on a.name=b.name and a.ssn=b.ssn;
quit;

Re: Update values in a table if columns match a second table?

Ksharp — Wed, 15 Mar 2017 01:47:45 GMT

data names;
input name $ ssn $ invited $;
datalines;
john 10 n
phil 12 n
charles 13 n
;
data donations;
input name $ ssn $ donated;
datalines;
joe 23 1000
george 18 4599
phil 12 6553
;

proc sql;
	update names as a
	set invited='y'
	where exists(select * from donations where 
a.nam
e=donations.name and a.ssn=donations.ssn);
quit;