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-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
  • 2 replies
  • 1404 views
  • 0 likes
  • 3 in conversation