topic Re: How to reduce space utilisation during joins in proc sql? in SAS Programming

How to reduce space utilisation during joins in proc sql?

Rohit_Rai_1996 — Mon, 11 Apr 2022 15:20:16 GMT

I was joining 2 tables, 5 GB and 1GB in size respectively (5g left join 1g). (Both had around 1 million rows)

Took around 13 minutes, which moderate compared to queries I generally run.

During the join a file called "sastmp-000000111.sas7butl" was created which was 127 GB in size.

My access was blocked and now I have to raise a ticket to get it unlocked, with an optimised query to perform the same task.

But I have no clear idea on what would be an optimal query.

I've heard about indexes, but not sure if they would be useful, or how to use them here.

Re: How to reduce space utilisation during joins in proc sql?

ballardw — Mon, 11 Apr 2022 15:26:29 GMT

You would have to show the code for suggestions that apply to your job.

Generic advice: reduce the size as early as possible. One example is to only select variables needed early on.

If data xxx has 400 variables and you only need 4 instead of using

from xxx

use:

from (select var1, var2, var3, var4 from xxx) as x

If you know that only need some of the records apply where clauses before joining for example

from (select var1, var2, var3, var4 from xxx
         where varxxx in (<list of values>) and varyyy ge '01JAN2022'd ) as x

Re: How to reduce space utilisation during joins in proc sql?

Rohit_Rai_1996 — Mon, 11 Apr 2022 16:01:53 GMT

I understand.

In this case all variables are needed (45 on one side, 20 on the other, total 67).

Re: How to reduce space utilisation during joins in proc sql?

Kurt_Bremser — Mon, 11 Apr 2022 16:02:26 GMT

The best way to optimize a SQL query is to not use SQL. Unless you need to do a many-to-many join, a data step MERGE will outperform SQL and consume considerably less space during the necessary sorts. The MERGE itself needs only space for the resulting dataset.

Depending on the number (and type and size) of variables taken from the 1G dataset, you might even be able to do it with no preceding sort and a hash object.

If you show us your SQL code, we can suggest how to translate it to data step code.

Re: How to reduce space utilisation during joins in proc sql?

Rohit_Rai_1996 — Tue, 12 Apr 2022 05:03:17 GMT

Thanks, I didn't realise merge was better.

Code was :
proc sql;
create table AB as
select*
from Table1 a
left join Table2 b
on a.key=b.key;
quit;

Re: How to reduce space utilisation during joins in proc sql?

Kurt_Bremser — Tue, 12 Apr 2022 06:29:18 GMT

SELECT * with at least one variable in common between the two datasets will result in a WARNING, so it would be sloppy programming at best.

Please post your REAL code, so we can see which variables from which dataset will contribute to the final result. If sensitive variable names are in there, replace them with consistent XXX, YYY and so on. But it is imperative to know from where the variables come from.

Re: How to reduce space utilisation during joins in proc sql?

Rohit_Rai_1996 — Tue, 12 Apr 2022 06:50:51 GMT

I actually used SELECT* in the real code.

There are 45 variables in the left table, 20 variables in the right table. We need all the variables in the final table

2 or 3 are common. AFAIK, if the variable is already present in left, then it isn't picked up from right.

Re: How to reduce space utilisation during joins in proc sql?

Kurt_Bremser — Tue, 12 Apr 2022 07:14:53 GMT

You DO NOT WANT WARNINGs in your LOG, ever. PERIOD. Good code runs without ERRORs, WARNINGs or any NOTEs beyond those telling you how long a step took and which datasets it read and/or created. So you do not use a global asterisk in a SQL join. You may use a.* to select all variables from one dataset, but you must be selective for the variables coming from other datasets to avoid name collisions. In case of a FULL/OUTER join, you must take care to COALESCE the ON variable(s), or you'll get unwanted missing values.

Any code that throws a WARNING (or an ERROR) will not run successfully from a scheduler, so it's unusable in a professional context.

If you have a one-to-one, a one-to-many, or a many-to-one relationship, these SORT/DATA steps can replace your SQL:

proc sort data=table1;
by key;
run;

proc sort data=table2;
by key;
run;

data ab;
merge
  table1 (in=a)
  table2 (drop=....) /* drop the variables that are common, except key */
;
by key;
if a; /* keep only observations coming from table1 */
run;

Depending on the state of your datasets, the SORT steps may not be necessary.

Re: How to reduce space utilisation during joins in proc sql?

Patrick — Tue, 12 Apr 2022 09:14:50 GMT

@Rohit_Rai_1996 wrote:
I actually used SELECT* in the real code.
2 or 3 are common. AFAIK, if the variable is already present in left, then it isn't picked up from right.

...or may-be not and you could end-up with an undesired result.

data table1;
  set sashelp.class(drop=age rename=(name=key));
run;

data table2;
  set sashelp.class(rename=(name=key));
  if mod(_n_,2)=0;
run;

proc sql feedback;
  create table AB as
    select*
  from Table1 a
    left join Table2 b
      on a.key=b.key;
quit;

Even though Sex, Age, Height and Weight also exist in Table1 FEEDBACK tells us they get picked up from TABLE2.

But then looking at the result table it seems only AGE gets really picked-up from TABLE2 - at least with my version of SAS.

That FEEDBACK doesn't tell us the "truth" is ugly and looks like an undocumented feature. If I would have a say then SAS wouldn't just throw a WARNING but an ERROR for such cases. That's what all the databases I know would be doing.

Re: How to reduce space utilisation during joins in proc sql?

Rohit_Rai_1996 — Tue, 12 Apr 2022 11:02:50 GMT

Thanks. I'll try this

Re: How to reduce space utilisation during joins in proc sql?

Rohit_Rai_1996 — Tue, 12 Apr 2022 11:15:47 GMT

Oh

I'll try to pay attention to this