topic Error trying to make a left join in SAS Data Management

Error trying to make a left join

fri0 — Wed, 03 Apr 2019 15:36:14 GMT

HI, I'm doing a left join using a proc sql but I get this error: ERROR: Sort key definition failure.

This is a query that I've executed before without no problems:

proc sql;
create table work.data as select
a.codunicocli,
b.codclavecic,
b.apepatcli,
b.apematcli,
b.nbrcli
from work.febrero as a
left join ods_v.md_cliente as b on a.codunicocli=b.codunicocli;
quit;

Any suggestion to solve it? Thanks!

PD. I'm using SAS Enterprise Guide 7.1

Re: Error trying to make a left join

JBailey — Wed, 03 Apr 2019 20:08:19 GMT

Hi @fri0

This error usually occurs when SAS runs out of a resource. I would verify that SAS work has sufficient space.

Best wishes,
Jeff

Re: Error trying to make a left join

fri0 — Wed, 03 Apr 2019 21:24:04 GMT

I copied the second table to a table in the work library and the query works... This has no sense to me, if the problem would be space, I couldn't make a copy of any table right?

Re: Error trying to make a left join

JBailey — Thu, 04 Apr 2019 18:26:13 GMT

Hi @fri0

Join processing can cause an increase in the size of data that is written to work. I am not sure where the space issue actually is, but this type of error is always a "Hey, I can't get enough of something" problem.

Best wishes,
Jeff

Re: Error trying to make a left join

Patrick — Sat, 06 Apr 2019 03:52:55 GMT

@fri0

A SQL left join requires implicit sorting of your source tables and though will create temporary utility files which might consume a multiple of the source table disc space. So just being able to copy a source table to WORK doesn't proof that you've got enough space.

System option UTILLOC points often to WORK.

To get as much WORK space as possible start a new session or delete tables in WORK before you run your SQL.