Help using Base SAS procedures

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

Reply
N/A
Posts: 0

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

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#
Super Contributor
Posts: 359

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

Posted in reply to deleted_user
The best approach here is to create a temporary table in DB2 to do the join.
Occasional Contributor
Posts: 11

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

Posted in reply to deleted_user
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
Valued Guide
Posts: 2,177

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

Posted in reply to deleted_user
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
Super User
Posts: 5,434

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

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
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 124 views
  • 0 likes
  • 5 in conversation