topic Re: Delete data from one table, linking to another table using SQL in SAS Procedures

Delete data from one table, linking to another table using SQL

deleted_user — Mon, 08 Nov 2010 20:54:30 GMT

I have two tables:
ResidentA and AddressDel

both tables contain the fields cur_street_address1 and cur_city_name

I am trying to write a SQL code to delete the records in ResidentA that are in AddressDel by cur_street_address1 and cur_city_name

Is there anyway to do this?

Here is what i have, and i know it is wrong. I use to do SQL on an AS400 mainframe and this is one i just cant figure out.

proc sql;
delete p.* from mylib.ResidentAIDS as p, mylib.FederalPrisonWV as q
where p.cur_street_address1 = q.cur_street_address1 and
p.cur_city_name = q.cur_city_name;
run;

Re: Delete data from one table, linking to another table using SQL

Grim — Tue, 09 Nov 2010 00:04:42 GMT

I can think of a few approaches:

Approach One: This assumes that cur_street_address1 and cur_city_name are strings with the same definition between tables.
Proc sql;
delete from mylib.ResidentA
where cur_street_address1 || cur_city_name in (select cur_street_address1 || cur_city_name from mylib.AddressDel);
quit;

Approach Two: This assumes there is a unique ID (say U_ID) that can be mapped between the two tables.

Proc sql;
delete from mylib.ResidentA
where U_ID in (select U_ID from mylib.ResidentA a
inner join mylib.AddressDel d
where a.cur_street_address1 = d.cur_street_address1 and
a.cur_city_name = d.cur_city_name);
quit;

Approach Three: Why not just create a new table....

Re: Delete data from one table, linking to another table using SQL

Ksharp — Tue, 09 Nov 2010 07:43:59 GMT

Hi.Recommend you to post up some dummy data and what data do you need.
Not origin data structure ,it is hard to code.
As what you write .Consider use proc sql 's 'except' operation .

[pre]
data temp;
set sashelp.class;
where sex eq 'F';
run;
proc sql feedback;
select * from sashelp.class
except
select * from temp;
quit;
[/pre]

Ksharp

Re: Delete data from one table, linking to another table using SQL

Patrick — Tue, 09 Nov 2010 09:41:10 GMT

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

Re: Delete data from one table, linking to another table using SQL

deleted_user — Tue, 09 Nov 2010 14:30:43 GMT

Thanks for the ideas everyone. I used Grim's approach 1 and it worked. I did not know you could use a select within the statement.

Thanks!!!