topic Re: ERROR: WORK.table2 is an undefined name in SAS Enterprise Guide

ERROR: WORK.table2 is an undefined name

Boswser — Mon, 29 Nov 2021 13:25:06 GMT

Good morning everyone, I’m having a little trouble with what should be a simple PROC SQL CREATE TABLE in SAS EG.

I imported an excel document into SAS EG and used a CREATE TABLE statement to make it into a table in SAS EG called TABLE2.

I’m now trying to join it with a table in DB2, but am getting the error: work.table2 is an undefined name. SQLSTATE=42704. This is my code:

———

%loginmacro;
proc sql;

CONNECT TO db2(login stuff);

CREATE TABLE table3 as
SELECT * from connection to db2(
SELECT
A.*,
B.date
FROM
work.table2 a
INNER JOIN
schema.db2table b ON
a.ID = b.ID
) ;

Disconnect from db2;
Quit;

————
So table2 definitely exists because I did another program that simply was CREATE TABLE test as SELECT * from work.table2, just to make sure I wasn’t crazy, and it worked. But the above code gives me the undefined error.

Is there something in the ordering I am doing wrong?

Thank you!

Re: ERROR: WORK.table2 is an undefined name

PaigeMiller — Mon, 29 Nov 2021 15:40:56 GMT

Show us the complete (all of it, every single line) LOG of both programs. (Which one did you run first?)

Please copy the LOG as text and paste it into the window that appears when you click on the </> icon here in the SAS Communities.

Re: ERROR: WORK.table2 is an undefined name

Tom — Mon, 29 Nov 2021 15:51:37 GMT

So you created a SAS dataset and then asked DB2 to read from it? That cannot work.

If you want your DB2 code to access data you need to move the data into DB2.

Re: ERROR: WORK.table2 is an undefined name

Boswser — Mon, 29 Nov 2021 16:16:40 GMT

That makes sense. Unfortunately I am not able to upload data to DB2, and the DB2 table I am trying to match my imported data onto is many millions of rows, so pulling that DB2 table into SAS so it shows up under WORK will probably be too taxing for the system. I’ll have to find some other way. Thank you!

Re: ERROR: WORK.table2 is an undefined name

Tom — Mon, 29 Nov 2021 16:44:16 GMT

How many ids are in the WORK table? If the list is small enough you might be able to use a macro variable to build your WHERE condition.

Do you want all of the values from the DB2 table for the ID's in your WORK table?

proc sql noprint;
select distinct ID into :list seprated by ',' from work.table2;
CONNECT TO db2(login stuff);
CREATE TABLE table3 as
  SELECT * from connection to db2
    (SELECT B.*
     FROM schema.db2table b
     where b.id in (&list)
    )
 ;
quit;

If the ID variable is character instead of numeric then change select distinct id into to select distinct quote(trim(id),"'") into .

If you just want the list of matching ids then change SELECT B.* to select distinct b.id .