topic Re: Error: At least one of the IDs values (element 0 which is 0) is invalid in SAS Programming

Error: At least one of the IDs values (element 0 which is 0) is invalid

altijani — Sun, 25 Aug 2024 02:31:36 GMT

I am receiving this message repeatedly after a simple code like this: What would be the cause?

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_ah AS
SELECT DISTINCT

t1.Store_ID,
t1.Customer_ID,
t1.CLUSTER_ID
FROM adm.Jumb_ah t1
ORDER BY t1.CLUSTER_ID;
QUIT;

Re: Error: At least one of the IDs values (element 0 which is 0) is invalid

Astounding — Sun, 25 Aug 2024 04:54:25 GMT

An untested guess: change your ORDER BY clause. Remove "t1." so it simply reads:
ORDER BY CLUSTER_ID;

Re: Error: At least one of the IDs values (element 0 which is 0) is invalid

altijani — Sun, 25 Aug 2024 11:21:14 GMT

Still the same error.

Re: Error: At least one of the IDs values (element 0 which is 0) is invalid

Quentin — Sun, 25 Aug 2024 13:23:08 GMT

That's a weird message. What does the log show?

What happens if you run the code without the create table clause, i.e.:

PROC SQL;
SELECT DISTINCT
t1.Store_ID,
t1.Customer_ID,
t1.CLUSTER_ID
FROM adm.Jumb_ah t1
ORDER BY t1.CLUSTER_ID;
QUIT;

Is the source data (adm.Jumbh_ah) a SAS dataset, or some relational database?

In the part of the screen shot that you blacked out, what data set is the message referring to? Is it adm.Jumb_ah or work.Query_For_ah or something else?

Could adm.Jumb_ah be a database view with integrity constraints, rather than a table? And when you query from adm.Jumb_ah one of the integrity constraints fails?