Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 962
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: 962
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 Fileaid 2 commands instream work. In ... descann Compuware & Other Tools 2 Tue May 16, 2017 3:31 pm
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts LISTCAT on a dataset with symbolic pa... shreya19 JCL & VSAM 5 Fri Apr 07, 2017 5:43 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 Fri Mar 24, 2017 2:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us