topic Re: Less resource intensive joins in SAS Programming

Less resource intensive joins

tomcmacdonald — Wed, 28 Mar 2018 16:45:04 GMT

I'm trying to join 17 tables together in SAS using PROC SQL and am running out of system resources. All tables have a simple index on the joined field. I'm thinking I can use the DATA STEP but I need to mimic COALESCE and left joins.

Re: Less resource intensive joins

Reeza — Wed, 28 Mar 2018 16:47:20 GMT

@tomcmacdonald wrote:

I'm trying to join 17 tables together in SAS using PROC SQL and am running out of system resources.

Are you joining all in single query or multiple queries?

Re: Less resource intensive joins

tomcmacdonald — Wed, 28 Mar 2018 16:48:28 GMT

In a single query.

Re: Less resource intensive joins

tomcmacdonald — Wed, 28 Mar 2018 19:32:14 GMT

Now I'm trying to run the code in UNIX batch mode and specifying -MEMSIZE MAX and -WORK <some directory> system options.

Re: Less resource intensive joins

Patrick — Wed, 28 Mar 2018 19:50:15 GMT

@tomcmacdonald

In my experience SAS SQL is not great when trying to join many tables in a single SQL. Depending on what you want to do and especially what volumes and relationships between the tables you're dealing with, some tweaks to the SQL or a SAS data step can reduce resource consumption and/or improve performance a lot.

In case this is one big table with a lot of lookups (i.e. a fact table with dimension and reference tables) then using a SAS data step with hash lookup tables can be much more efficient. Logically left joins and coalesce() can also get implemented in a SAS data step.

Re: Less resource intensive joins

Reeza — Wed, 28 Mar 2018 20:11:19 GMT

If it's a fact-dim reconstruction, I'm a fan of formats as well. They also tend to come in handy in other areas so you may even have them built already.

Re: Less resource intensive joins

LinusH — Thu, 29 Mar 2018 08:29:18 GMT

The problem here is the left joins, since SAS (and other) can optimize the as god as inner joins.

Usually when creating star schemas, dimension values that are missing can be assigned a dummy value, like a dimsnion key -1. Then you can switch to inner joins.

If your data is in Base SAS libraries consider moving to SPDE, which has better index management and some additional use cases for index use in the where planner.