In general, macro is orders of magnitude slower than data step, SQL, or RDBMS joins. Especially if you're writing huge amounts of data to the macro symbol table. Since your data is already in a dataset or table, just use it from there. Following on from @ErikLund_Jensen 's post, I ran the below code as a simple test to see which would run faster: data have;
do x=1 to 1E7;
random=ranuni(0);
output;
end;
run;
data list;
do y=1 to 1E8 by 3;
random=ranuni(0);
output;
end;
run;
proc sort data=have;
by random;
run;
proc sort data=list;
by random;
run;
%bench(start)
proc sql _method;
create table want1 as
select x
from have
where x in (
select y from list
)
order by x;
quit;
%bench(end)
%bench(start)
proc sql _method;
create table want2 as
select x
from have
join list
on have.x=list.y
order by x;
quit;
%bench(end) want1: Total time: 00 hours, 02 minutes, 08 seconds 127.50099992752 want2: Total time: 00 hours, 00 minutes, 41 seconds 41.3870000839233 SQL execution methods: want1: sqxcrta
sqxsort
sqxfil
sqxsrc( WORK.HAVE )
NOTE: SQL subquery execution methods chosen are:
sqxsubq
sqxsrc( WORK.LIST ) want2: Because the data was so narrow and would fit in memory, SQL used a hash join: NOTE: SQL execution methods chosen are:
sqxcrta
sqxsort
sqxjhsh
sqxsrc( WORK.LIST )
sqxsrc( WORK.HAVE ) Hope this helps!
... View more