BookmarkSubscribeRSS Feed
brulard
Pyrite | Level 9

hello,

 

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 a.event_date=b.date 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,

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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 a.event_date=b.date;
quit;

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.

kiranv_
Rhodochrosite | Level 12

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

 

 

paper for hash join

http://analytics.ncsu.edu/sesug/2011/BB08.Lafler.pdf

 

paper for join with proc format

http://www.lexjansen.com/phuse/2007/cc/CC02.pdf

 

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

http://lexjansen.com/nesug/nesug10/cc/cc37.pdf

 

 

Kurt_Bremser
Super User

First, create a sorted sub-dataset:

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

Then, do a data step merge on the tables:

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

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

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2892 views
  • 4 likes
  • 5 in conversation