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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.