Desktop productivity for business analysts and programmers

SQL Server Functions using Proc SQL?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

SQL Server Functions using Proc SQL?

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!


Accepted Solutions
Solution
‎04-17-2015 09:49 AM
Esteemed Advisor
Posts: 6,646

Re: SQL Server Functions using Proc SQL?

Try pass-through SQL.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎04-17-2015 09:49 AM
Esteemed Advisor
Posts: 6,646

Re: SQL Server Functions using Proc SQL?

Try pass-through SQL.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 15

Re: SQL Server Functions using Proc SQL?

Thanks Kurt! Worked perfectly!

Community Manager
Posts: 2,693

Re: SQL Server Functions using Proc SQL?

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

Occasional Contributor
Posts: 15

Re: SQL Server Functions using Proc SQL?

Thanks! very helpful!

Contributor
Posts: 23

Re: SQL Server Functions using Proc SQL?

Just to remove an assumption: can the "{fn " in many of those be ignored?
Community Manager
Posts: 2,693

Re: SQL Server Functions using Proc SQL?

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 645 views
  • 7 likes
  • 4 in conversation