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: 8593
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: 8593
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: 965
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: 965
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 Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Mainframe dataset split purushottam DFSORT/ICETOOL 13 Tue Aug 29, 2017 12:28 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm

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