topic Re: Error TempIndx in SAS Programming

Error TempIndx

Ahsan — Thu, 11 Jan 2018 16:57:51 GMT

Hi I try to run this code but I got an error how do we resolve this

PROC SQL;

25 CREATE TABLE ahsan AS

26 SELECT

27 Distinct

28 SERVICE_YEAR_MONTH,

29 BILL_PRVDR_NPI,

30 BILL_PRVDR_ID,

31 BILL_PRVDR_EIN,

32 MBR_ADMIN_CNTY_DESC,

33 BILL_PRVDR_CNTY_DESC,

34 MBR_RES_CNTY_DESC,

35 SVC_PRVDR_CNTY_DESC,

36 CA1_CA2,

37 RT_COHORT_AMT,

38 COUNT(DISTINCT MBR_ID) AS BENEFICIARIES,

39 SUM(PAID_AMT) AS TOTAL_AMOUNT_PAID

40 FROM KELLY_MEDICAID_TRANS_CA_COUNTY

41 GROUP BY

41 ! SERVICE_YEAR_MONTH,BILL_PRVDR_NPI,CA1_CA2,RT_COHORT_AMT,BILL_PRVDR_EIN,MBR_ADMIN_CNTY_DESC,BILL_PRVDR_CNTY_DESC,MBR_RES_C

41 ! NTY_DESC,SVC_PRVDR_CNTY_DESC

42 ORDER BY SERVICE_YEAR_MONTH,BILL_PRVDR_NPI,CA1_CA2,RT_COHORT_AMT;

ERROR: Index TEMPINDX cannot be created on file WORK.'SASTMP-000000048'n because the length of the index value (16096 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).

ERROR: :Unable to create temporary index while processing summary functions.

Re: Error TempIndx

kiranv_ — Thu, 11 Jan 2018 17:05:06 GMT

check this link below

http://support.sas.com/kb/48/733.html

Re: Error TempIndx

error_prone — Thu, 11 Jan 2018 17:05:30 GMT

I can't see the commands creating an index, but this could be a problem caused by viewing this page on my phone. The error suggests two things two solve the issue, increasing index buffer size seems to be the easier way.

Re: Error TempIndx

kiranv_ — Thu, 11 Jan 2018 17:09:37 GMT

After some search, what I find this could be memory issue.

Do you really need order by. order by needs lot of work under the hood. Unless you use it for some purpose, I would remove order by and run the query.

Re: Error TempIndx

Ahsan — Thu, 11 Jan 2018 17:45:06 GMT

I removed order by statement still getting the error

Re: Error TempIndx

Ahsan — Thu, 11 Jan 2018 17:46:30 GMT

how do we increase the buffer size

Re: Error TempIndx

kiranv_ — Thu, 11 Jan 2018 18:03:51 GMT

how large is the dataset? does your group by and variables you mention in select are same?

Re: Error TempIndx

Ahsan — Thu, 11 Jan 2018 18:10:52 GMT

it wasn't same but I corrected and still getting the error

Re: Error TempIndx

Ahsan — Thu, 11 Jan 2018 18:22:50 GMT

19+ million rows that's how large the dataset is

Re: Error TempIndx

lkjsdalfkjsd — Thu, 13 Sep 2018 16:49:48 GMT

check the length of your columns, i had the same issue and a colesce put my length to 8000. once i fixed length it ran fine.

Re: Error TempIndx

simeonthomas2 — Mon, 21 Mar 2022 02:44:52 GMT

I encountered a similar situation recently and the issue was that some of the attributes used in the groupby clause were very large strings. Limiting the length of the characters in these attributes fixed the issue for me. For example I reduced the limit of character length from 2000 to 50.