Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SAS - Can we join SAS dataset with DB2 tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
hemanth11

New User


Joined: 08 Feb 2006
Posts: 2
Location: Bangalore

PostPosted: Fri Sep 12, 2008 11:34 am    Post subject: SAS - Can we join SAS dataset with DB2 tables
Reply with quote

Hi,

I have a requirement in SAS.
There are around 15,000 records in a file. For every record, I need to verify that this particular record exists in a DB2 table. The DB2 table has millions of records.
I can think of unloading the entire table contents and merge with the input file. But this requires all the million records to be unloaded and is a performance issue.

Please help me.

Thanks,
Hemanth
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8657
Location: Back in jolly old England

PostPosted: Fri Sep 12, 2008 11:46 am    Post subject:
Reply with quote

I suppose you could try something like
Code:

proc sql noprint;                 

  CONNECT TO DB2 (SSID = DB2_id);
                                   
  Create table  work.whatever  as                                   
  select distinct a.key, b.key, b.abc, b.def
  from  sas_data as a, db2_table as b                                 
  where (a.key = b.key);         

  DISCONNECT FROM DB2;
                                                                     

I'm sure there are plenty of people out there that may improve my SQL icon_lol.gif
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Fri Sep 12, 2008 11:50 am    Post subject: Reply to: SAS - Can we join SAS dataset with DB2 tables
Reply with quote

You can use SAS PROC SQL.
Back to top
View user's profile Send private message
hemanth11

New User


Joined: 08 Feb 2006
Posts: 2
Location: Bangalore

PostPosted: Fri Sep 12, 2008 12:20 pm    Post subject:
Reply with quote

Hi,
Is there any other option with "Connect to DB2" statement to have the Creator also to be set.??
I tried this with CREATOR.TABLENAME and it is taking CREATOR as SAS Library name.
Here is the snapshot of this part:

8 PROC SQL NOPRINT;
8 !
9 CONNECT TO DB2 (SSID=DSN);
9 !
10 CREATE TABLE IORDER AS
11 SELECT A.NPA, A.NNX, A.DIGITS, B.BUS_OFC_ID_CD
12 FROM WORK.FILE1 A
13 LEFT OUTER JOIN D2UJV2A0.TCP401 B
14 ON A.NNX = B.NNX_LENI
15 AND A.DIGITS = B.DIGITS;
ERROR: Libname D2UJV2A0 is not assigned.
ERROR: Libname D2UJV2A0 is not assigned.
ERROR: Libname D2UJV2A0 is not assigned.

Thanks,
Hemanth
expat wrote:
I suppose you could try something like
Code:

proc sql noprint;                 

  CONNECT TO DB2 (SSID = DB2_id);
                                   
  Create table  work.whatever  as                                   
  select distinct a.key, b.key, b.abc, b.def
  from  sas_data as a, db2_table as b                                 
  where (a.key = b.key);         

  DISCONNECT FROM DB2;
                                                                     

I'm sure there are plenty of people out there that may improve my SQL :lol:
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8657
Location: Back in jolly old England

PostPosted: Fri Sep 12, 2008 1:00 pm    Post subject:
Reply with quote

Your FROM statement
FROM WORK.FILE1 A

My FROM statement
from sas_data as a, db2_table as b

Not too sure about my SQL coding abilities, but this code does come from a working query.
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 972
Location: Richmond, Virginia

PostPosted: Sat Sep 13, 2008 8:19 am    Post subject:
Reply with quote

Can you create a DB2 table with your 15K recs?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Sep 13, 2008 8:22 am    Post subject: Reply to: SAS - Can we join SAS dataset with DB2 tables
Reply with quote

H Phil,

Good to "see" you. . . icon_wink.gif

d
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 972
Location: Richmond, Virginia

PostPosted: Sat Sep 13, 2008 7:57 pm    Post subject:
Reply with quote

Thanks Dick.

I'm a Data Analyst (SAS Programmer) with a major credit card company now, so I thought I'd check on SAS inquiries occasionally.

Why no SAS topic?

Greetings to all.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Sep 14, 2008 2:23 am    Post subject: Reply to: SAS - Can we join SAS dataset with DB2 tables
Reply with quote

Hi Phil,

Quote:
Why no SAS topic?
I'm not sure. . . Possibly there were not many sas topics when the categories were established? I would think it might go in the Programming Languages part of the index?

Looks like sas topics are being posted/moved in several different parts of the forum. . .

You might want to forum SEARCH on SAS rather than looking only at the subject/title.

Hope the new work is going well. Always great to "see" you icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How could i share my personal dataset... James Halley TSO/ISPF 1 Fri Mar 23, 2018 9:41 pm
This topic is locked: you cannot edit posts or make replies. How could i share my personal dataset... samenxia163 TSO/ISPF 1 Fri Mar 09, 2018 12:18 pm
No new posts Where to get sample for processing RM... vice_versa PL/I & Assembler 2 Tue Jan 16, 2018 2:03 pm
No new posts VSAM dataset not found abdulrafi JCL & VSAM 5 Fri Jan 12, 2018 11:19 am
No new posts To get Non matching row after compari... vinu78 DB2 7 Fri Jan 05, 2018 5:43 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us