I'm always a bit uneasy to combine an explicit join with a where clause and then trust the SAS SQL optimizer that it doesn't first execute the join and only then applies the where condition on the intermediary result set.
In your case I'd go for an implicit join with the "date" filter first.
data have_1;
do row_id=1 to 100000;
name=put(row_id,z10.);
if mod(row_id,500)=0 then dt='201812';
else dt=' ';
output;
end;
stop;
run;
data have_2;
set have_1(keep=name);
run;
proc sql _method feedback;
create table want_1 as
select a.*
from
have_1 a
,have_2 b
where
a.dt>'201712'
and a.name ne ' '
and a.name=b.name
;
quit;
Please note that if you've got the same name multiple times in both tables (=a many to many relationship) then you could end-up with a lot of rows which could be the reason for you long running SQL.
As for not using a data step merge because of the pre-sorting required: Your SQL will also have to sort the tables.
I've added in the code above option _method to the SQL. This shows you in the log what your SQL is actually doing. With my sample data it's a hash join - but this will be different with your big tables and it's highly likely that you'll see SQXSORT.
And just as a thought: If you just want to select all rows in the one table that have a matching name in another table then creating a distinct list of names first would be the way to go (avoiding a many to many situation). ...and if this distinct list of names is not too big then you could eventually even use a SAS data step hash lookup for this which would avoid any need for sorting the data.
... View more
![SASGF 2020 [Virtual] Attendee](/html/@4229D83652827A3CCCDB8CAD5D23E8D2/badge_icons/badge_sasgf20_attendee.png "SASGF 2020 [Virtual] Attendee"){kind=icon}
