Blogs

Blogs

Using Microsoft Excel functions in SAS

29
By Topics | Learn SAS Programming Tips

SAS Microsoft partnershipYou 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:

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 as user-defined functions (see section PROC FCMP and Microsoft Excel). These functions are predefined for you and their definitions are stored in the SASHELP.SLKWXL data table provided with your SAS installation. You can generate a list of these functions by running the following code:

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:

List of Excel functions available in SAS (via SASHELP.SLKWXL)
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 datdif datdif4_slk ( start, end ) **** NOTE: a subset of datdif for DAYS only
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 = 5.9;
   y = odd_slk(x);
   put 'odd( ' x ') = ' y;
run;

SAS log:
odd( 5.9 ) = 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:
even( 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 x 7 x 1 x 2 x 2 = 140.

MULTINOMIAL function

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

MULTINOMIAL(a1, a2, ... , an) = (a1 + a2 + ... + an)! : (a1! a2! ... an!)

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! x 3! x 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 and statistical 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

Your thoughts?

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

Tags Data step programmers Microsoft Excel SAS functions SAS Programmers
Share

About Author

Leonid Batkhan

Leonid Batkhan is a long-time SAS consultant and blogger. Currently, he is a Lead Applications Developer at F.N.B. Corporation. He holds a Ph.D. in Computer Science and Automatic Control Systems and has been a SAS user for more than 25 years. From 1995 to 2021 he worked as a Data Management and Business Intelligence consultant at SAS Institute. During his career, Leonid has successfully implemented dozens of SAS applications and projects in various industries. All posts by Leonid Batkhan >>>

29 Comments

  1. Andrea Zimmerman on

    Perfect, I have a client new to SAS, but very adept at Excel. I often find that it helps if I can talk to them in Excel, but this will help be our Rosetta Stone 🙂

    Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Thank you, Andrea, great observation. And this bridge works both ways: it can be Rosetta Stone for "native Excel speakers" to learn SAS, as well as for "native SAS speakers" to learn Excel 😉

      Reply
  2. Bruce Gilsen on

    Thanks Leonid, this is very helpful. I hope you can present this as a conference paper or perhaps a Super Demo once we start having conferences in person again.

    Reply
  3. Bruce Gilsen on

    That would be excellent. I do think these functions have value and am very glad you've publicized them.

    I do have one more question. I'm doing a little internal writeup on these, and included a summary of each function from https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm25. 40 of the 41 functions are described there, but I can't find DATDIF4 there or by googling for it. That page does have DATDIF. DATDIF4 is definitely the name in SASHELP.

    Thanks,
    Bruce

    Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Thank you, Bruce. Indeed, there is DATDIF() function in MS Excel, but DATDIF4_SLK() function in SAS. The original DATDIF() function has 3 arguments (3rd argument is unit - DAY, MONTH, YEAR,...), however, DATDIF4_SLK has only 2 arguments as it implements a subset of DATDIF() for DAYS only. I made corresponding correction in the table and added NOTE to it.

      Reply
    • Leonid Batkhan
      Leonid Batkhan on

      As far as I remember that disappeared post was just a list of Excel functions available in SAS. Now, with this blog post the same listing (augmented with the corresponding SAS names and arguments) is included here, so maybe the doc's link needs to be updated to point to this blog post.

      Reply
  5. Bruce Gilsen on

    Leonid:
    Hi, thanks for clarifying this. Just one followup question. You mention above that:
    Luckily, many of them are made available in SAS via PROC FCMP as user-defined functions (see section PROC FCMP and Microsoft Excel).

    I clicked the PROC FCMP link and in the section PROC FCMP and Microsoft Excel there's a link for: You can view these functions at Excel functions in SAS.
    That link took me to the SAS Communities home page. Various searches there didn't yield anything obvious. Do you know what it's trying to point me to?

    Thanks,
    Bruce

    Reply
  6. Bruce Gilsen on

    Hi Leonid. It's been a while since we sat next to each other on a flight back from a conference.

    This feature is really interesting and I'd not heard of it. Two questions
    1. Are we calling the actual Excel functions, or SAS re-writes of them?

    2. If the former, I see that a lot of the financial functions deal with dates and wondered how are they being handled. If I pass in SAS dates, are they being translated to account for January 1, 1960 being 0 in SAS and January 1, 1900 being 1 in Excel? I realize that a lot (all?) of them involve a range of dates so if the number of dates is the same whatever the "0" date is, it might not matter. But it might in some cases.

    Thanks,
    Bruce

    Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Hi Bruce, great memories!

      These are not "actual" Excel functions, but rather SAS incarnations of them. SAS implemented (re-wrote) them as user-defined functions using PROC FCMP. Therefore, one can expect dates being treated as SAS date values. Hope this clarifies it for you.

      Best regards,
      Leonid

      Reply
  7. Matthew on

    Very interesting, some useful gems here. I'm particularly surprised there isn't a native "product" function in SAS; if there were I think it would see use.

    The requirement to specify the arguments as an array, as for product_slk is a downside since it means the function can't be used in contexts where arrays are unavailable (say, PROC SQL).

    Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Thank you, Matthew, for your feedback. I consider your first sentence as a vote for implementing product function as a native SAS function.
      In PROC SQL you don't need an array and therefore product function to multiply columns, you can do that by listing them separated with '*' (multiplication operator): COL1 * COL2 * COL3 ... * COLn.

      Reply
  8. Daniel on

    Ok: neat, handy and cool, BUT! Why does SAS not implement its own functions instead if SAS believes they are so important?

    Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Hi Daniel, thank you for your comment. SAS is a customer-centric organization. If you as a SAS user believe these functions need be natively implemented, say it. Meanwhile, SAS made it available to the SAS user community as user-defined functions.

      Reply
  9. Cristina on

    Hi Leonid, do you know if I can compress the size of a file that gets exported using the XLSX engine? Thank you in advance,

    Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Hi, Cristina! Here is an example of how you can compress (zip) your .xlsx file (or any file) on Windows:

      /* create xlsx workbook */
libname MYXLSX XLSX "c:\temp\sashelpdata.xlsx";
proc copy in=SASHELP out=MYXLSX;
   select AIR CARS;
run;

/* zip xlsx workbook */
filename inf "c:\temp\sashelpdata.xlsx" recfm=n;
filename outf zip 'c:\temp\sashelpdata.xlsx.zip' member='sashelpdata.xlsx' recfm=n ;
%let rc = %sysfunc(fcopy(inf,outf));
%put &=rc;

      Is that what you were looking for?

      Reply
  10. Jerry Voight on

    This is yet another hidden SAS gem that I would not have stumbled upon. Thanks for revealing this to us Leonid!

    Reply

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top