Help using Base SAS procedures

Proc SQL Delete Very Slow

Reply
Contributor
Posts: 58

Proc SQL Delete Very Slow

Hi!!

I have two tables:

table1 - 10,000,000 rows

Key: cd1, cd2, cd3, cd4

table2 - 30,000 rows

Key: cd1, cd2

And I want to run the sql below:

proc sql;

delete

from table1 a

where exists (select 1 from table2 b where a.cd1=b.cd1 and a.cd2=b.cd2)

;

This sql took more than three hours and did not finish.

table1 and table2 are SAS tables.

Does anyone have any tips on how to optimize this SQL?

Valued Guide
Posts: 3,208

Re: Proc SQL Delete Very Slow

optimizing.... then do not uses SQL. 

SQL is designed to have the machine decide. When that decisions are not as wanted the human brain should do his work. 

---->-- ja karman --<-----
Super User
Posts: 9,671

Re: Proc SQL Delete Very Slow

I would like to use data step ,when the table is very very big. For you scenario, I recommend Hash Table.

Xia Keshan

Ask a Question
Discussion stats
  • 2 replies
  • 304 views
  • 0 likes
  • 3 in conversation