Evan, You are using the SQL Pass-through facility. It works like this.

* You make a connection to a database. * You use SAS SQL to pass external-database-specific SQL code to the external database. That's the part in your parentheses.

Below, you are using MONOTONIC(), which is SAS SQL code, where you are supposed to be using your database-specific SQL code. You can fix this my moving the WHERE statement to the external code. Try something like

Proc sql ; Connect to odbc as myDb ( dataSrc="MVSDB2P" user=PDRSL password=XXXXXXXX ) ; Create table acts as select * from connection to myDb ( select * from DMDB.SIS_DEGREE_V ) where ( monotonic() le 10 ) ; Quit ;

I know that this will cause all of the data file to be transported from the external database to SAS and then be evaluated, but I know of no other way to use MONOTONIC() here. I don't know DB2; there might be a row-counter variable in that system that you can use directly.

I am intently working on a proposal and do not have time to test the above code. MONOTONIC() is not documented and there are some pecularities about it, so you should be careful that you get what you want.

Ed

Edward Heaton, Senior Systems Analyst, Westat (An Employee-Owned Research Corporation), 1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195 Voice: (301) 610-4818 Fax: (301) 294-3992 mailto:EdwardHeaton@westat.com http://www.westat.com

-----Original Message----- From: Evan Davies [mailto:edavies@wm.edu] Sent: Wednesday, February 27, 2002 12:39 PM To: Edward Heaton Subject: sql monotonic

Hi - I read about the monotonic criteria from your post on SAS-L. Can you tell me what I'm doing wrong here invoking it? Thanks, Evan.

proc sql; 195 connect to odbc as mydb 196 (datasrc="MVSDB2P" user=PDRSL password=XXXXXXXX); 197 create table acts as 198 select * from connection to mydb 199 (select * 200 from DMDB.SIS_DEGREE_V 201 where ( monotonic() le 10) ) ; ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0104N An unexpected token ")" was found following "". Expected tokens may include: "( + - ? : USER COUNT <FLOAT> CURRENT <DECIMAL> <INTEGER> ". SQLSTATE=42601

202 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used: real time 1.30 seconds

Evan Davies Senior Analyst Office of Institutional Research The College of William and Mary Phone: 757 221-2147 FAX: 757 221-2080 http://www.wm.edu/ir