Passing SAS Functions to JDBC

SAS/ACCESS Interface to JDBC passes down database-specific functions that take scalar values as arguments to your data source. The list of functions that SAS passes down is a subset of the X/Open Group CLI specification. If a data source supports a scalar function, its driver is also expected to support this function using JDBC escape syntax. That is, the specified function name is passed down in the format {fn <function-name>}.

Where the JDBC function name differs from the SAS SQL function name, the JDBC name appears in parentheses. For more information, see Passing Functions to the DBMS Using PROC SQL.

** (POWER)

LOG

ABS

LOG10

ACOS

LOWCASE (LCASE)

ASIN

MAX

ATAN

MIN

ATAN2

MINUTE

AVG

MONTH

CAT (CONCAT)

CEIL (CEILING)

QTR (QUARTER)

CHAR

RADIANS

COS

RAND

COT

SECOND

COUNT

SIGN

DATE (CURDATE)

SIN

DATETIME (CURTIME or NOW – see Note)

SOUNDEX

DTEXTDAY (DAYNAME)

SQRT

DTEXTMONTH (MONTHNAME)

SUBSTR

DTEXTWEEKDAY (DAYOFWEEK)

SUM

EXP

TAN

FLOOR

TRIMN (RTRIM)

HOUR

UPCASE (UCASE)

INDEX (LOCATE)

WEEK

LEFT (LTRIM)

YEAR

LENGTH

Note: The DATETIME function passes either the CURTIME or NOW function, depending on the database that you are connecting to using JDBC. You can specify SASTRACE=’,,,d’ and MSGLEVEL=i in the OPTIONS statement to see which function is passed to your database. For more information, see Tracing and Evaluating SQL Generation.

SQL_FUNCTIONS=ALL allows for SAS functions that have slightly different behavior from corresponding database functions that are passed down to the database. Only when SQL_FUNCTIONS=ALL can the SAS/ACCESS engine also pass these SAS SQL functions to JDBC. Due to incompatibility in date and time functions between JDBC and SAS, JDBC might not process them correctly. Check your results to determine whether these functions are working as expected.

MOD

REPEAT

ROUND

Last updated: February 3, 2026