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
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
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?
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.