proc sql;
delete from DATA1 a
where exists (select 1 from DATA2 b where a.ID = b.ID and a.ID2 = b.ID2);
quit;
This statement is tremendously slow for a table with 8.3 million rows.
Two questions:
First, the obvious one. How "slow" is really slow? How long is it taking?
Second, what kind of tables are "DATA1" and "DATA2". SAS datasets? Database tables accessed via SAS/Access? Other?
And if they're database tables, which technology (Oracle, DB2...)
Tom
@mgm wrote:
proc sql; delete from DATA1 a where exists (select 1 from DATA2 b where a.ID = b.ID and a.ID2 = b.ID2); quit;
This statement is tremendously slow for a table with 8.3 million rows.
- define "slow". Use options fullstimer to get a clear picture.
- given that all datasets reside in WORK, and the SQL utility file will also be placed there, I'm not surprised that the performance of this step is dismal. Depending on the size of data2, I'd either use sort/data steps, or a format created from data2, or a hash object created from data2.
proc sql;
delete from DATA1 a
where exists (select 1 from DATA2 b where a.ID = b.ID and a.ID2 = b.ID2);
quit;
This statement is tremendously slow for a table with 8.3 million rows, any suggestions on improving the performance ?
With 8.3M rows I would have thought either indexing the ID and ID2 fields on both files or pre-sorting them by ID and ID2 might help.
I would try Hash Table if the table is very big.
Or try this one , maybe save you some time.
proc sql;
delete from DATA1
where catx('|',ID,ID2) in (select catx('|',ID,ID2) from DATA2 );
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.