SAS SQL table vs DB2 table

[deleted_user]

Hello everyone,

I am dumping the content of a SAS dataset B into a SQL table S as below. This code works.

PROC SQL NOPRINT;
CREATE TABLE S AS
SELECT GIFTID, LEGAL_EID, ENTITYID, KEY1
FROM B
ORDER BY ENTITYID;

Now I would like to Select entity id's of equal value from a real DB2 table (TB926) and the table from above (S) as follows:

SELECT *
FROM TB926 A,
S
WHERE A.ENTITYID = S.ENTITYID
;

This code does not work. Can this be done? Any input will be greatly appreciated!

Thank you so much !

[ChrisNZ]

everything in your code points to tables in the work library, and i don't see passthru code.

maybe you simplified your code a bit too much for us to see what you are after?

[LinusH]

If something does not work, please attach some log, or this will be guessing contest.
To answer your question: yes, it could be done, and should work if it's done properly.

/Linus

[deleted_user]

Sorry guys. Let me try this again.

I have created a SAS dataset called B. It contains GiftID and EID.
Next I am dumping the content of sas dataset B into a table called TEMP:

PROC SQL NOPRINT;
CREATE TABLE TEMP AS
SELECT GIFTID, EID
FROM B
ORDER BY EID;

Everything works up to this point. Next I would like to find the matching records between table TEMP and a real DB2 table called TB926. Here is the query:

SELECT *
FROM TB926 A,
TEMP B
WHERE A.EID = B.EID
;

This is the error msg I get with this query:
"ADSKYC.TEMP IS AN UNDEFINED NAME"

"ADSKYC" is my part of my user ID. I guess SAS adds it to the table name. Am I clear about what I am trying to accomplish here? How would I find matching records between a SAS table and a DB2 table?

Thanks again!

[sbb]

Clearly there is more to your SAS program flow that you are sharing - it would be best to COPY/PASTE the SAS log output (including any code leading up to the PROC SQL invocation), otherwise the debugging assistance process for forum subscribers will be a guessing-game as to what additional code you have and what SAS diagnostic messages you are receiving.

Scott Barry
SBBWorks, Inc.

[LinusH]

Using the system options SASTRACE and SASTRACELOC could be essential, since they will show you the exact SQL syntax that is performed by DB2.
If you are in ZOS, there are a bulk of other DB2 specific options you can play with to get more information about your SQL.
/Linus

[Flip]

From this bit of the log you are submitting the second querry :
SELECT *
FROM TB926 A,
TEMP B
WHERE A.EID = B.EID
;
as a passthru. This means that it is actually being processed by the DB2 instance not SAS. Thus it is looking for TEMP in DB2 where it does not exist. It is DB2 adding your user name to TEMP which gives this away. You will need to assign a libname to DB2 and run this querry in SAS not a passthru to get the resluts you desire.