BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
4 REPLIES 4
Grim
Calcite | Level 5
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....
Ksharp
Super User
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
Patrick
Opal | Level 21
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
deleted_user
Not applicable
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!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 9171 views
  • 1 like
  • 4 in conversation