BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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 !
6 REPLIES 6
ChrisNZ
Tourmaline | Level 20
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
Tourmaline | Level 20
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
Data never sleeps
deleted_user
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Tourmaline | Level 20
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
Data never sleeps
Flip
Fluorite | Level 6
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 834 views
  • 0 likes
  • 5 in conversation