ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000001416'n because the length

Satora_In — Mon, 11 Dec 2023 10:22:40 GMT

Dear all,

I have a problem in my code. I miss understood.

%_eg_conditional_dropds(WORK.daily_contacts);

PROC SQL;

CREATE TABLE WORK.daily_contacts AS

SELECT DISTINCT t1.dt FORMAT=ddmmyy10. LENGTH=8 LABEL='' AS dt,

t1.USER_ID_HASH LENGTH=40 AS USER_ID_HASH,

t1.ADVERT_ID LENGTH=20 AS ADVERT_ID,

(1) LENGTH=8 AS cnt_contact,

t2.cat LENGTH=40 AS cat,

t2.cat_2 LENGTH=50 AS cat_2,

t2.cat_3 LENGTH=50 AS cat_3

FROM WORK.MARKET_EVENTS t1

INNER JOIN SANDBOX.SSS_ t2 ON (t1.ADVERT_ID = t2.ID)

WHERE t1.USER_ID_HASH NOT IS MISSING AND t1.ADVERT_ID NOT IS MISSING AND t1.dt >= &rep_dt AND t1.dt < &w_end;

QUIT;

ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000001416'n because the length of the index value (32767 bytes) is too

large. The index page size (currently 32767) must be large enough to store three index values plus a small overhead

structure. Reduce the length of the index value or use the IBUFSIZE= option to increase the index page size (up to a maximum

of 32,767 bytes).

Re: ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000001416'n because the length

Patrick — Mon, 11 Dec 2023 12:15:19 GMT

Looks like you're hitting this issue: https://support.sas.com/kb/48/733.html

Does SAS continue to run and do you get the table created as the SAS Note indicates?

From the SAS Note:
"Applying the hot fix prevents the SQLRC return code from being set to a non-zero value and allows the query to continue processing using a non-hash join method."

The SAS Note doesn't mention it but may-be using option magic could instruct the SQL optimizer to use a non-hash join method from start avoiding some error message being written to the log.
https://support.sas.com/resources/papers/proceedings14/1240-2014.pdf

magic=102 sounds promising.

topic ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000001416'n because the length in SAS Programming

ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000001416'n because the length

Re: ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000001416'n because the length