Hey guys,
I tried searching for any previous comments on this, but nothing quite the same came up... I have a User defined function in SQL server for converting Julian dates to Gregorian Dates, and another for Jaro Winkler Probability (distance), The question is, Would there be a way to call these functions in SAS on a proc sql statement? I tried putting them on the statement but SAS cannot find the function it says the function cannot be located..
Any suggestions? I know that for the Julian I can process in SAS but I would like to keep the processing on the server side..I am more interested in the other function..
Thanks!
Try pass-through SQL.
Try pass-through SQL.
Thanks Kurt! Worked perfectly!
When not using pass-through, you can get a list of SAS functions that will be "translated" to database functions from the LIBNAME statement using SQL_FUNCTIONS_COPY=. Example (for ODBC):
SAS Function Mappings provided by SAS ACCESS engine:
SAS DBMS
FUNCTION NAME FUNCTION NAME
------------- -------------
LOWCASE {fn LCASE
UPCASE {fn UCASE
ABS {fn ABS
ARCOS {fn ACOS
ARSIN {fn ASIN
ATAN {fn ATAN
CEIL {fn CEILING
COS {fn COS
EXP {fn EXP
FLOOR {fn FLOOR
LOG {fn LOG
LOG10 {fn LOG10
SIGN {fn SIGN
SIN {fn SIN
SQRT {fn SQRT
TAN {fn TAN
COSH COSH
SINH SINH
TANH TANH
DTEXTDAY {fn DAYOFMONTH
DTEXTMONTH {fn MONTH
DTEXTYEAR {fn YEAR
DTEXTWEEKDAY {fn DAYOFWEEK
YEAR {fn YEAR
MONTH {fn MONTH
DAY {fn DAYOFMONTH
HOUR {fn HOUR
MINUTE {fn MINUTE
SECOND {fn SECOND
WEEKDAY {fn DAYOFWEEK
QTR {fn QUARTER
BYTE {fn CHAR({fn CONVERT
TRIMN {fn RTRIM
MOD {fn MOD({fn CONVERT
STRIP {fn RTRIM({fn LTRIM
SUBSTR {fn SUBSTRING
TRANWRD {fn REPLACE
INDEX {fn LOCATE
LENGTH {fn LENGTH
REPEAT {fn REPEAT
LEFT {fn LTRIM
Thanks! very helpful!
@JohnJPS - yes, of course. Not sure why those appeared in the log, but you can assume that just the main function name is relevant here.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.