topic Re: Join eating up all available resources in SAS Programming

Join eating up all available resources

tomcmacdonald — Mon, 17 Sep 2018 17:06:35 GMT

It has worked in the past and now suddenly my code is broken. I'm performing a join between ~20 tables with around 1-50k rows each. All tables have an index and are sorted. The join is performed using PROC SQL. It's requiring hundreds of GB's before taking up all available resources and throwing an error once everything is exhausted. I'm trying to see if there's a cartesian product being mistakenly created and there are none. What are my options? Must I refactor this to be done with PROC SORT's and DATA steps?

Re: Join eating up all available resources

SuryaKiran — Mon, 17 Sep 2018 17:18:17 GMT

Did you check some of the basic performance considerations for your query like WHERE clause, KEEP or DROP, indexed datasets.

Re: Join eating up all available resources

tomcmacdonald — Mon, 17 Sep 2018 17:21:56 GMT

How would I go about doing that? For this query there is no WHERE clause. And isn't KEEP and DROP specific to the DATA step and not PROC SQL?

Re: Join eating up all available resources

SuryaKiran — Mon, 17 Sep 2018 17:44:14 GMT

You can use KEEP or DROP or SELECT Clause in your query for keeping only the columns that you need.

Keep drop can also be used in proc sql

proc sql;
select *
from sashelp.class(keep= Name Sex);
quit;

Also another consideration is avoid heterogeneous joins if possible. Where are your tables located? and share your sample query.

Re: Join eating up all available resources

tomcmacdonald — Mon, 17 Sep 2018 17:48:17 GMT

proc sql;
  create table report as 
  select
    a.id,
    a.col1,
    a.col2,
    b.col4
    case when
        c.col5 is not null then 1
    end as col5
    ...
    z.col30
  from
    a
    left join b on a.id = b.id
    ...
    left join z on a.id = z.id;
quit;

The query looks something like that. Some tables are in work and some are in other libraries.

Re: Join eating up all available resources

Kurt_Bremser — Mon, 17 Sep 2018 18:26:01 GMT

If you do not have a many-to-many relationship and therefore do not need to build a cartesian join, switch to sorting and merging in a data step.

Re: Join eating up all available resources

tomcmacdonald — Mon, 17 Sep 2018 19:09:43 GMT

OK, I figured it out, this was my mistake. It was forming a cartesian product because I forgot to throw out rows with a NULL variable that it was being joined on. Thanks for the help everyone.