Join a very large table with a small table - Efficient Way

sas
proc_sql

#1

Hi!

I have a table with 80 lacs plus rows and the other table with 50 rows.
I want to join both the table fetch columns from the large table for only 50 keys in the small table.

Right now I’m using join to fetch the data but the execution time is too much around 19 hours.

Is there any way I can run the query on large table for only those 50 keys in small table.

I want to know if there is any other efficient way using SAS or Proc SQL or SQL.


#2

Fahad,
Here is an easier way to do this. You can store the 50 key-value pairs in a macro array using something like symputx.
Now subset the 80lacs table on each key-value pair using a for loop and add a variable in the data step which can hold the value macros array. Here is a framework how to do this :
%do i = 1 %to 50;
data x;set x;
if key = &&key&i. then value = &&value&i.;
run;

Hope this helps,
Tavish


#3

Hi @tavish_srivastava ,

I am also looking for solution to this problem, can you please help me more with code.
It would be great if you explain the entire code to me.

Still learning SAS. :frowning:

Pretty urgent.