Error TempIndx

[Ahsan]

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.

[kiranv_]

check this link below

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

[error_prone]

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.

[kiranv_]

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.

[Ahsan]

I removed order by statement still getting the error

[kiranv_]

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

[Ahsan]

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

[Ahsan]

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

[Ahsan]

how do we increase the buffer size

[lkjsdalfkjsd]

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.

[simeonthomas2]

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.