BookmarkSubscribeRSS Feed
Ahsan
Calcite | Level 5

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.

10 REPLIES 10
kiranv_
Rhodochrosite | Level 12

check this link below

 

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

 

error_prone
Barite | Level 11
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_
Rhodochrosite | Level 12

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
Calcite | Level 5
I removed order by statement still getting the error
kiranv_
Rhodochrosite | Level 12

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

Ahsan
Calcite | Level 5
it wasn't same but I corrected and still getting the error
Ahsan
Calcite | Level 5
19+ million rows that's how large the dataset is
Ahsan
Calcite | Level 5
how do we increase the buffer size
lkjsdalfkjsd
Calcite | Level 5

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 6269 views
  • 1 like
  • 5 in conversation