topic Using SAS Dataset as part of In Clause of PROC SQL to DB2 in SAS Procedures

Using SAS Dataset as part of In Clause of PROC SQL to DB2

deleted_user — Tue, 04 May 2010 17:10:39 GMT

Hello.

I am attempting to use a 100 record dataset to limit the accounts from a DB2 connection into a table holding 100s of Millions.

Currently, I am hard coding 7 of the accounts in the In(), but would like to use a SAS Dataset in place of. Please help.

proc sql ;
connect to db2(%login) ;

create table Library.Table as
select *
from connection to db2
(
Select *
From schema.table
Where Acct In (X,X,X,X,X,X,X)
)
;

disconnect from db2;
quit;

Where X = an Acct#

Re: Using SAS Dataset as part of In Clause of PROC SQL to DB2

Flip — Tue, 04 May 2010 17:56:50 GMT

The best approach here is to create a temporary table in DB2 to do the join.

Re: Using SAS Dataset as part of In Clause of PROC SQL to DB2

sfsdtegsdsdgdffhgfh — Tue, 04 May 2010 18:24:29 GMT

you can try
1) subquery to create list of customer id's
2) create macro variable/s of customer id's from local sas table and use in sql query

HTH

Re: Using SAS Dataset as part of In Clause of PROC SQL to DB2

Peter_C — Wed, 05 May 2010 20:00:09 GMT

since there are no more than 100 observations in your SAS table, put the account column into a macro variable to use as the in-list, like:[pre] proc sql noprint ;
select distinct acct
into : in_list separated by ', '
from sas_table
;
%put have &sqlobs in the &in_list ;

create table Library.Table as
select * from connection to db2
(
Select * From schema.table
Where Acct In( &in_list )
)
; [/pre]
I've used the technique successfully for 1000-item lists.
The macro variable is resolved before the syntax is passed through SAS/Access to the data base.

good luck
PeterC

Re: Using SAS Dataset as part of In Clause of PROC SQL to DB2

LinusH — Thu, 06 May 2010 11:31:20 GMT

Another way is to use the DBKEY= ds option, and using implicit pass-thru (using a DB2 LIBANME instead).

Cold work, and is definitely easier to code. Of course Acct needs to be indexed in DB2.

/Linus