ERROR: WORK.table2 is an undefined name

[Boswser]

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!

[PaigeMiller]

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.

[Tom]

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.

[Boswser · Accepted solution]

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!

[Tom]

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 .