SQL Server Functions using Proc SQL?

[Josers18]

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!

[Kurt_Bremser · Accepted solution]

Try pass-through SQL.

SAS(R) 9.2 Language Reference: Concepts, Second Edition

[Josers18]

Thanks Kurt! Worked perfectly!

[ChrisHemedinger]

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):

libname jivedb odbc
  datasrc="jivedb"
  sql_functions_copy=saslog;

Log output:

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

[Josers18]

Thanks! very helpful!

[JohnJPS]

Just to remove an assumption: can the "{fn " in many of those be ignored?

[ChrisHemedinger]

@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.