BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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#
4 REPLIES 4
Flip
Fluorite | Level 6
The best approach here is to create a temporary table in DB2 to do the join.
sfsdtegsdsdgdffhgfh
Fluorite | Level 6
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
Peter_C
Rhodochrosite | Level 12
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
LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 797 views
  • 0 likes
  • 5 in conversation