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

[Satora_In]

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).

[Patrick]

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.