Hi
The way this is often solved in SQL is to concatenate the columns, i.e:
data ResidentA;
othervar=1;
cur_city_name = 'City 1';
cur_street_address1 = 'Street 1';
output;
cur_city_name = 'City 1';
cur_street_address1 = 'Street 2';
output;
cur_city_name = 'City 2';
cur_street_address1 = 'Street 1';
output;
cur_city_name = 'City 2';
cur_street_address1 = 'Street 2';
output;
run;
data AddressDel;
cur_city_name = 'City 1';
cur_street_address1 = 'Street 2';
output;
cur_city_name = 'City 1';
cur_street_address1 = 'Street 3';
output;
run;
proc sql;
delete from ResidentA
where cats(cur_city_name,'|',cur_street_address1) in
( select cats(cur_city_name,'|',cur_street_address1) from AddressDel )
;
quit;
proc print data=ResidentA;
run;
I used a '|' as separator when concatenating the variables. Use whatever character or string you like. Just make sure that when combining vars that the combined string is only possible for the row(s) where the variables come from. Best use a character which you're sure that it's not used in any of the variables.
By the way:
If the data is in SAS tables then a more efficient way would be to load the AddressDel into a hash with the 2 fields as key and then just to delete all records in ResidentA where the check() method doesn't find a match. You could even implement this with a modify statement which means only one pass through ResidentA with change in place.
HTH
Patrick
Message was edited by: Patrick