topic Re: SQL Server Functions using Proc SQL? in SAS Enterprise Guide

SQL Server Functions using Proc SQL?

Josers18 — Fri, 17 Apr 2015 13:45:55 GMT

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!

Re: SQL Server Functions using Proc SQL?

Kurt_Bremser — Fri, 17 Apr 2015 13:49:55 GMT

Try pass-through SQL.

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

Re: SQL Server Functions using Proc SQL?

Josers18 — Fri, 17 Apr 2015 14:05:00 GMT

Thanks Kurt! Worked perfectly!

Re: SQL Server Functions using Proc SQL?

ChrisHemedinger — Fri, 17 Apr 2015 18:10:05 GMT

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 

Re: SQL Server Functions using Proc SQL?

Josers18 — Fri, 17 Apr 2015 18:55:11 GMT

Thanks! very helpful!

Re: SQL Server Functions using Proc SQL?

JohnJPS — Thu, 01 Jun 2017 20:35:31 GMT

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

Re: SQL Server Functions using Proc SQL?

ChrisHemedinger — Fri, 02 Jun 2017 12:44:23 GMT

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