topic Re: Passing parameters to SQL query in SAS Procedures

Passing parameters to SQL query

deleted_user — Wed, 14 Oct 2009 13:56:24 GMT

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.

Re: Passing parameters to SQL query

garybald — Wed, 14 Oct 2009 16:17:56 GMT

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.

Re: Passing parameters to SQL query

ChrisNZ — Wed, 14 Oct 2009 20:54:57 GMT

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.

Re: Passing parameters to SQL query

DanielSantos — Thu, 15 Oct 2009 09:31:48 GMT

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

Re: Passing parameters to SQL query

LinusH — Thu, 15 Oct 2009 13:36:41 GMT

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