You might have heard about SAS - Microsoft partnership announced in June 2020 that officially joined the powers of SAS analytics with Microsoft’s cloud technology to further advance Artificial Intelligence (AI).

This partnership did not just happen out of nowhere. SAS has a long and deep history of integrating with Microsoft technologies. Examples include:

- SAS Add-In for Microsoft Office, which extends Microsoft Office to use the power of SAS data access, analysis, and reporting directly from Microsoft Outlook, Excel, Word, and PowerPoint;
- The ability to use SAS with Microsoft 365 (OneDrive, Teams, and SharePoint).

In this post we will look at a lesser known, but quite useful feature in SAS that allows SAS users to bring many Microsoft Excel functions right to their SAS programs. I hope that many SAS users (not just MS Excel aficionados) will love to discover this functionality within SAS.

**Excel functions as SAS user-defined functions**

SAS has a wide variety of built-in functions, however there are still many Microsoft Excel functions that are not intrinsically implemented in SAS. Luckily, many of them are made available in SAS via

proc fcmp inlib=SASHELP.SLKWXL listall; run; |

You can also capture the list of available Excel functions in a SAS data table using ODS OUTPUT with CODELIST= option:

ods noresults; ods output codelist=WORK.EXCEL_FUNCTIONS_LIST (keep=COL1 COL2); proc fcmp inlib=SASHELP.SLKWXL listall; run; ods output close; ods results; |

From this data table you can produce a nice looking HTML report listing all these functions:

data WORK.EXCEL_SAS_FUNCTIONS (keep=exc sas arg); label exc='Excel Function' sas='SAS Function' arg='Arguments'; set WORK.EXCEL_FUNCTIONS_LIST (rename=(col2=arg)); sas = tranwrd(col1,'Function ',''); exc = tranwrd(sas,'_slk',''); run; ods html path='c:\temp' file='excel_sas_functions.html'; title 'List of Excel functions available in SAS (via SASHELP.SLKWXL)'; proc print data=EXCEL_SAS_FUNCTIONS label; run; ods html close; |

When you run this code, you should get the following list of Excel functions along with their SAS equivalents:

Obs | Excel Function | SAS Function | Arguments |
---|---|---|---|

1 | even | even_slk | ( x ) |

2 | odd | odd_slk | ( x ) |

3 | factdouble | factdouble_slk | ( x ) |

4 | product | product_slk | ( nums ) |

5 | multinomial | multinomial_slk | ( nums ) |

6 | floor | floor_slk | ( n, sg ) |

7 | datdif4 | datdif4_slk | ( start, end ) |

8 | amorlinc | amorlinc_slk | ( cost, datep, fperiod, salvage, period, rate, basis ) |

9 | amordegrc | amordegrc_slk | ( cost, datep, fperiod, salvage, period, rate, basis ) |

10 | disc | disc_slk | ( settlement, maturity, pr, redemp, basis ) |

11 | tbilleq | tbilleq_slk | ( settlement, maturity, discount ) |

12 | tbillprice | tbillprice_slk | ( settlement, maturity, discount ) |

13 | tbillyield | tbillyield_slk | ( settlement, maturity, par ) |

14 | dollarde | dollarde_slk | ( fdollar, frac ) |

15 | dollarfr | dollarfr_slk | ( ddollar, frac ) |

16 | effect | effect_slk | ( nominal_rate, npery ) |

17 | coupnum | coupnum_slk | ( settlement, maturity, freq, basis ) |

18 | coupncd | coupncd_slk | ( settlement, maturity, freq, basis ) |

19 | coupdaysnc | coupdaysnc_slk | ( settlement, maturity, freq, basis ) |

20 | couppcd | couppcd_slk | ( settlement, maturity, freq, basis ) |

21 | coupdays | coupdays_slk | ( settlement, maturity, freq, basis ) |

22 | db | db_slk | ( cost, salvage, life, period, month ) |

23 | yield | yield_slk | ( settlement, maturity, rate, pr, redemp, freq, basis ) |

24 | yielddisc | yielddisc_slk | ( settlement, maturity, pr, redemp, basis ) |

25 | coupdaybs | coupdaybs_slk | ( settlement, maturity, freq, basis ) |

26 | oddfprice | oddfprice_slk | ( settlement, maturity, issue, fcoupon, rate, yield, redemp, freq, basis ) |

27 | oddfyield | oddfyield_slk | ( settlement, maturity, issue, fcoupon, rate, pr, redemp, freq, basis ) |

28 | oddlyield | oddlyield_slk | ( settlement, maturity, linterest, rate, pr, redemp, freq, basis ) |

29 | oddlprice | oddlprice_slk | ( settlement, maturity, linterest, rate, yield, redemp, freq, basis ) |

30 | price | price_slk | ( settlement, maturity, rate, yield, redemp, freq, basis ) |

31 | pricedisc | pricedisc_slk | ( settlement, maturity, discount, redemp, basis ) |

32 | pricemat | pricemat_slk | ( settlement, maturity, issue, rate, yld, basis ) |

33 | yieldmat | yieldmat_slk | ( settlement, maturity, issue, rate, pr, basis ) |

34 | received | received_slk | ( settlement, maturity, investment, discount, basis ) |

35 | accrint | accrint_slk | ( issue, finterest, settlement, rate, par, freq, basis ) |

36 | accrintm | accrintm_slk | ( issue, maturity, rate, par, basis ) |

37 | duration | duration_slk | ( settlement, maturity, coupon, yld, freq, basis ) |

38 | mduration | mduration_slk | ( settlement, maturity, coupon, yld, freq, basis ) |

39 | avedev | avedev_slk | ( data ) |

40 | devsq | devsq_slk | ( data ) |

41 | varp | varp_slk | ( data ) |

**NOTE:** Excel functions that are made available in SAS are named from their Excel parent functions, suffixing them with **_SLK** to distinguish them from their Excel incarnations, as well as from native SAS functions.

**Examples of Microsoft Excel functions usage in SAS**

In order to use any of these Excel functions in your SAS code, all you need to do is to specify the functions definition data table in the CMPLIB= option:

`options cmplib=SASHELP.SLKWXL;`

Let’s consider several examples.

## ODD function

This function returns number **rounded up** to the nearest odd integer:

options cmplib=SASHELP.SLKWXL; data _null_; x = 6.4; y = odd_slk(x); put 'odd( ' x ') = ' y; run; |

SAS log:

`odd( 6.4 ) = 7`

## EVEN function

This function returns number **rounded up** to the nearest even integer:

options cmplib=SASHELP.SLKWXL; data _null_; x = 6.4; y = even_slk(x); put 'even( ' x ') = ' y; run; |

SAS log:

`odd( 6.4 ) = 8`

## FACTDOUBLE function

This function returns the **double factorial** of a number. If number is not an integer, it is truncated.

Double factorial (or semifactorial) of a number *n*, denoted by *n!!*, is the product of all the integers from 1 up to *n* that have the same parity as *n*.

For even *n*, the double factorial is **n!!=n(n-2)(n-4)…(4)(2)**, and for odd *n*, the double factorial is **n!! = n(n-2)(n-4)…(3)(1)**.

Here is a SAS code example using the **factdouble()** Excel function:

options cmplib=SASHELP.SLKWXL; data _null_; n = 6; m = 7; nn = factdouble_slk(n); mm = factdouble_slk(m); put n '!! = ' nn / m '!! = ' mm; run; |

It will produce the following SAS log:

`6 !! = 48`

`7 !! = 105`

Indeed, 6!! = 2 x 4 x 6 = 48 and 7!! = 1 x 3 x 5 x 7 = 105.

## PRODUCT function

This function multiplies all elements of SAS numeric array given as its argument and returns the product:

options cmplib=SASHELP.SLKWXL; data _null_; array x x1-x5 (5, 7, 1, 2, 2); p = product_slk(x); put 'x = ( ' x1-x5 ')'; put 'product(x) = ' p; run; |

SAS log:

`x = ( 5 7 1 2 2 )`

`product(x) = 140`

Indeed 5*7*1*2*2 = 140.

## MULTINOMIAL function

This function returns the ratio of the factorial of a sum of values to the product of factorials:

**MULTINOMIAL(a _{1}, a_{2}, ... , a_{n}) = (a_{1} + a_{2} + ... + a_{n})! : (a_{1}! a_{2}! ... a_{n}!)**

In SAS, the argument to this function is specified as numeric array name:

options cmplib=SASHELP.SLKWXL; data _null_; array a a1-a3 (1, 3, 2); m = multinomial_slk(a); put 'a = ( ' a1-a3 ')'; put 'multinomial(a) = ' m; run; |

SAS log:

`a = ( 1 3 2 )`

`multinomial(a) = 60`

Indeed (1+3+2)! : (1! + 3! + 2!) = 720 : 12 = 60.

**Other Microsoft Excel functions available in SAS**

You can explore other Excel functions available in SAS via **SASHELP.SLKWXL** user-defined functions by cross-referencing them with the corresponding Microsoft Excel functions documentation (alphabetical or by categories) As you can see in the above List of Excel functions available in SAS, besides mathematical functions exemplified in the previous section, there are also many Excel * financial functions related to securities trading that are made available in SAS*.

**Additional Resources on SAS user-defined functions**

- How to unquote SAS character variable values
- Finding n-th instance of a substring within a string
- Shifting a date by a given number of workdays

**Your thoughts?**

Have you found this blog post useful? Please share your use cases, thoughts and feedback in the comments below.

Using Microsoft Excel functions in SAS was published on SAS Users.