|
View previous topic :: View next topic
|
| Author |
Message |
hemanth11
New User
Joined: 08 Feb 2006
Posts: 2
Location: Bangalore
|
|
|
|
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 |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
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  |
|
| Back to top |
|
|
abhishekmdwivedi
New User
.jpg)
Joined: 22 Aug 2006
Posts: 95
Location: india
|
|
|
|
| You can use SAS PROC SQL. |
|
| Back to top |
|
|
hemanth11
New User
Joined: 08 Feb 2006
Posts: 2
Location: Bangalore
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
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 |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
| Can you create a DB2 table with your 15K recs? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
H Phil,
Good to "see" you. . . 
d |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Phil,
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 
d |
|
| Back to top |
|
|
|
|
