BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have a SQL query which needs parameters in where clause. I need to pass parameters from sas data set. Can I do this with "PROC DB2EXT IN=EMPLIST" where EMPLIST is sas dataset.
Ex: I have to read employee details from the db2 table for all the EMPNO in sas data set. Can someone advice me if I can do this with PROC DB2EXT? My query looks like this:
Select emp_name, emp_status from EMPLOYEE
WHERE Employee_NO = '%(&EMPLIST.EMPNO)' ;

I am executing SAS on OS/390 mainframe.
4 REPLIES 4
garybald
Calcite | Level 5
When I need to select accounts from a large DB2 table, I pass the account numbers that I have in a sas dataset using a proc format. I make sure I have one records per account, so I sort the data using the nodupkey option, then setup the sql like below.

other sas code that reads the accounts ...
PROC SORT DATA=ACCOUNTS OUT=NUM(KEEP=NUMBER) NODUPKEY;
BY NUMBER;

DATA TEMP (KEEP = FMTNAME TYPE LABEL START);
SET NUM;

START = NUMBER;
FMTNAME = 'ACCTF';
TYPE = 'N';
LABEL = 'KEEPME';

PROC FORMAT CNTLIN=TEMP;

PROC SQL;
SELECT *
FROM EMPLOYEE
WHERE PUT(NUMBER,ACCTF.) = 'KEEPME';

This selects only the account numbers that were included in the original dataset.
ChrisNZ
Tourmaline | Level 20
How about

sas sql:
select unique EMPNO into :EMPNOS separated by ',' from EMPLIST;

db2 passthough sql:
Select emp_name, emp_status from EMPLOYEE
WHERE Employee_NO in (&EMPNOS) ;

Limitation: &EMPNOS is limited to 64k ... but you can always use several lists if db2 supports longer sql statements.
DanielSantos
Barite | Level 11
Hi.

Chris's solution is great under the 64K limit, I would go for that.

Above that, best way would be to bulkload a temporary table with your list (into DB2), and then inner join it with the desired table. Of course, everything done with SQL Pass-Through.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
LinusH
Tourmaline | Level 20
Another (easier?) solution could be using the DBKEY= data set option:

http://support.sas.com/documentation/cdl/en/acreldb/61890/HTML/default/a001371553.htm

/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
  • 3809 views
  • 0 likes
  • 5 in conversation