BookmarkSubscribeRSS Feed
DavidCaliman
Calcite | Level 5

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?

2 REPLIES 2
jakarman
Barite | Level 11

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 --<-----
Ksharp
Super User

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

Xia Keshan

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1863 views
  • 0 likes
  • 3 in conversation