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.
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.
how large is the dataset? does your group by and variables you mention in select are same?
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.
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.