DATA Step, Macro, Functions and more

Error TempIndx

Reply
Contributor
Posts: 60

Error TempIndx

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.

PROC Star
Posts: 549

Re: Error TempIndx

check this link below

 

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

 

Regular Contributor
Posts: 226

Re: Error TempIndx

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.
PROC Star
Posts: 549

Re: Error TempIndx

[ Edited ]
Posted in reply to error_prone

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.

 

 

Contributor
Posts: 60

Re: Error TempIndx

I removed order by statement still getting the error
PROC Star
Posts: 549

Re: Error TempIndx

[ Edited ]

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

Contributor
Posts: 60

Re: Error TempIndx

it wasn't same but I corrected and still getting the error
Contributor
Posts: 60

Re: Error TempIndx

19+ million rows that's how large the dataset is
Contributor
Posts: 60

Re: Error TempIndx

Posted in reply to error_prone
how do we increase the buffer size
Ask a Question
Discussion stats
  • 8 replies
  • 370 views
  • 0 likes
  • 3 in conversation