DATA Step, Macro, Functions and more

help with inner join to very large table

Frequent Contributor
Posts: 123

help with inner join to very large table



i'm trying to do an inner join but when attempting to join to a particular table, i find myself aborting the query as it is takes too long, or I end up receiving an error message (will post when it appears again).


If anyone has coding suggestion (maybe there is a more efficient way of coding my join to a large table), please let me know.


Here is the code I used:


Proc sql; create table want as
Select a.*
From have as a, database_historical_table b where
a.ID_2=b.ID_2 and and b.ID_status='';quit;


My table "have", has only 30 records. The table "database_historical_table", refreshes daily for all of our card customers, so has billion of records.

ID2 is 16 text char.

The date range is within april '17.

The ID_status is 1 char.


Thank you,


Super User
Super User
Posts: 9,599

Re: help with inner join to very large table

I can't really tell from what you post.  Some test data in the form of a datastep and what the output should look like would go some ways to clarifying.  I would suspect having a smaller intermediary table first off would minimise the rows needed:

proc sql; 
  create table WANT as
  select  A.*
  from    HAVE A
  left join (select * from database_historical_table where ID_STATUS="") B 
  on      a.ID_2=b.ID_2 and;

That will shrink it, however if you have that many rows it will still be long.  SQL probably isnt the tool to do large data.  Show what you have and what you want and will be able to say more.

Posts: 512

Re: help with inner join to very large table

Instead of join. please use hash technique or proc format.



paper for hash join


paper for join with proc format


below is the paper which discusses various lookups something what you are doing.



Super User
Posts: 10,279

Re: help with inner join to very large table

First, create a sorted sub-dataset:

proc sort
  data=database_historical_table (
    keep=ID_2 date ID_status
    where=(ID_status = '')
  out=big_table (
by ID_2 event_date;

Then, do a data step merge on the tables:

data want;
  have (in=a)
  big_table (in=b)
by ID_2 event_date;
if a and b;

have should also be sorted first, of course.


The crucial thing is not to have proc sql build a monster utility file and do a gazillion of read/writes to/from that

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 8,120

Re: help with inner join to very large table

And an index to the large table.  Proc SQL should be able to use the index if it exists.

Ask a Question
Discussion stats
  • 4 replies
  • 5 in conversation