BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Josers18
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION
6 REPLIES 6
Josers18
Obsidian | Level 7

Thanks Kurt! Worked perfectly!

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Josers18
Obsidian | Level 7

Thanks! very helpful!

JohnJPS
Quartz | Level 8
Just to remove an assumption: can the "{fn " in many of those be ignored?
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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