Microsoft Excel

Using Microsoft Excel functions in SAS

 Data step programmers, Microsoft Excel, SAS functions, SAS programmers, Tech  No Responses »
10月 152020
 

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 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 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(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! + 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

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.

Accessing Excel files using LIBNAME XLSX

 Book, libname, little sas book, Microsoft Excel, sas press, Tech, xlsx  No Responses »
3月 102020
 

If you have been using SAS for long, you have probably noticed that there is generally more than one way to do anything. (For an example, see my co-author Lora Delwiche’s blog about PROC SQL.) The Little SAS Book has long covered reading and writing Microsoft Excel files with the IMPORT and EXPORT procedures, but for the Sixth Edition, we decided it was time to add two more ways: The ODS EXCEL destination makes it easy to convert procedure results into Excel files, while the XLSX LIBNAME engine allows you to access Excel files as if they were SAS data sets.

With the XLSX LIBNAME engine, you can convert an Excel file to a SAS data set (or vice versa) if you want to, but you can also access an Excel file directly without the need for a SAS data set. This engine works for files created using any version of Microsoft Excel 2007 or later in the Windows or UNIX operating environments. You must have SAS 9.4M2 or higher and SAS/ACCESS Interface to PC Files software. A nice thing about this engine is that it works with any combination of 32-bit and 64-bit systems.

The XLSX LIBNAME engine uses the first line in your file for the variable names, scans each full column to determine the variable type (character or numeric), assigns lengths to character variables, and recognizes dates, and numeric values containing commas or dollar signs. While the XLSX LIBNAME engine does not offer many options, because you are using an Excel file like a SAS data set, you can use many standard data set options. For example, you can use the RENAME= data set option to change the names of variables, and FIRSTOBS= and OBS= to select a subset of rows.

Reading an Excel file as is 

Suppose you have the following Excel file containing data about magnolia trees:

With the XLSX LIBNAME engine, SAS can read the file, without first converting it to a SAS data set. Here is a PROC PRINT that prints the data directly from the Excel file.

* Read an Excel spreadsheet using XLSX LIBNAME;
LIBNAME exfiles XLSX 'c:\MyExcel\Trees.xlsx';

PROC PRINT DATA = exfiles.sheet1;
   TITLE 'PROC PRINT of Excel File';
RUN;

Here are the results of the PROC PRINT. Notice that the variable names were taken from the first row in the file.

PROC PRINT of Excel File

Converting an Excel file to a SAS data set 

If you want to convert an Excel file to a SAS data set, you can do that too. Here is a DATA step that reads the Excel file. The RENAME= data set option changes the variable name MaxHeight to MaxHeightFeet. Then a new variable is computed which is equal to the height in meters.

* Import Excel into a SAS data set and compute height in meters;
DATA magnolia;
   SET exfiles.sheet1 (RENAME = (MaxHeight = MaxHeightFeet));
   MaxHeightMeters = ROUND(MaxHeightFeet * 0.3048);
RUN;

Here is the SAS data set with the renamed and new variables:


Writing to an Excel file 

It is just as easy to write to an Excel file as it is to read from it.

* Write a new sheet to the Excel file;
DATA exfiles.trees;
   SET magnolia;
RUN;
LIBNAME exfiles CLEAR;

Here is what the Excel file looks like with the new sheet. Notice that the new tab is labeled with the name of the SAS data set TREES.

The XLSX LIBNAME engine is so flexible and easy to use that we think it’s a great addition to any SAS programmer’s skill set.

To learn more about the content in The Little SAS Book, check out the free book excerpt.  To see up-and-coming titles and get exclusive discounts, make sure to subscribe to the SAS Books newsletter.

Accessing Excel files using LIBNAME XLSX was published on SAS Users.

Making the most of the ODS Excel destination

 Microsoft Excel, ODS Excel destination, sas author, sas ods, sas press, Tech, tips & techniques  No Responses »
4月 252019
 

As a SAS programmer, you are asked to do many things with your data -- reading, writing, calculating, building interfaces, and occasionally sending data outside of SAS. One of the most popular outputs you may be tasked with creating is likely a Microsoft Excel workbook. Have you ever heard, “just send me the spreadsheet”?

For an internal project the task is easy, just open the SAS ODS EXCEL destination, run PROC PRINT, and close SAS ODS EXCEL and the workbook spreadsheet is ready. But if the workbook or the spreadsheet is to be delivered somewhere else you may need to spruce it up a bit. Of course, you can manually change virtually everything on the spreadsheet, but that takes lots of employee time. And if the spreadsheet is delivered on a periodic basis, you may not run it the same every time.

Saving you time and money

Suppose you run a PROC PRINT with a “BY” statement and produce a Microsoft Excel workbook with 100 pages. If each of those pages need to be printed and distributed to 100 clients by mail, do you want to be the person who changes each of those to print as a landscape printout? The SAS ODS Excel destination has over 125 options and sub-options that can perform various tasks while the workbook is being written. One such task sets the worksheet to print in “landscape” format.

As a programmer, I know that when I want to start a new project or learn new software, I look to two places in a book: the index and the table of contents. If I can think of a key word that might help me, I look to the index. But when searching general topics, I use the Table of Contents (TOC). It always frustrates me if the TOC is in alphabetical order, so I decided to write my TOC as groups of options and SAS commands that impacted similar parts or features of the Excel Workbook.

To see this in action, the bullet points I have listed below identify the major topic sections of the book. These are, in fact, chapter titles presented after the introduction:

    • ODS Tagset versus Destination
    • ODS Excel Destination Actions
    • Setting Excel Document Property Values
    • Options That Affect the Workbook
    • Arguments that Affect Output Features
    • Options That Affect Worksheet Features
    • Options That Affect Print Features
    • Column, Row, and Cell Features

Take a look inside

Allow me to describe each of these topics in a few words:

    ODS Tagset versus Destination
    Many people have used the SAS ODS Tagset EXCELXP and will find many parts of the SAS ODS EXCEL destination to be very similar in both syntax and function. A tagset is Proc template code that can be changed by the user, while a SAS ODS destination is a built-in feature of SAS, much like a PROC or FUNCTION that cannot be changed by the users. This section also describes the ID feature of ODS which allows you to write more than one EXCEL workbook at a time.

    ODS Excel Destination Actions
    This area describes ODS features that may not be exclusive to ODS EXCEL but are useful in finding and choosing SAS outputs to be processed.

    Setting Excel Document Property Values
    Here you are shown how to change the comments, keywords, author, title, and other parts of the Excel Property sheet.

    Options That Affect the Workbook
    This section of the book shows you how to name the workbook, create blank worksheets, create a table of contents or index of worksheets within the workbook, change worksheet tab colors, and other options.

    Arguments that Affect Output Features
    The output features described here include changing the output style (coloration of the worksheet sections), finding and using stylesheet anchors, building and using Cascading Style Sheets, changing the Dots Per Inch (DPI) of the output data and or graphs, and adding text to the worksheet.

    Options That Affect Worksheet Features
    SAS has many options that describe the output data. These include titles, footnotes, and byline text. Additionally, SAS can group output worksheets in many ways including by page, by proc, by table, by by-group, or even no separation at all. Data can also be set to “FITTOPAGE” or the height or width can be selected along with adding sheet names or labels to the EXCEL output worksheets.

    Options That Affect Print Features
    Excel has many print features like printing in “black and white” only, centering horizontally or vertically, landscape or portrait, draft quality or standard, selecting the print order of the data, selecting the area to print, EXCEL headers and EXCEL footnotes, and others. All of which SAS can adjust as the workbook is being written.

    Column, Row, and Cell Features
    Finally, SAS can adjust column and row features like adding filters, changing the widths and heights or rows and columns, hiding rows or columns, inserting formulas, and even placing the data somewhere other than row one column one.

Ready to see the full Table of Contents? Click here.

Ready, set, go

My book Exchanging Data From SAS® to Excel: The ODS Excel Destination expands upon the SAS documentation by giving full descriptions and examples including SAS code and EXCEL output for nearly every option and sub-option of the SAS ODS EXCEL software. In addition to this blog, check out a free chapter of my book to get started making your worksheets beautifully formatted. Get ready to follow the money and make your reports come out perfect for publication in no time!

Making the most of the ODS Excel destination was published on SAS Users.

Control the location of tables, graphs, and text with ODS Excel

 Microsoft Excel, ODS Excel destination, output objects, Problem Solvers, SAS 9.4M3, Tech, xlsx  No Responses »
4月 242019
 
The ODS Excel destination, which became a production feature in SAS 9.4M3 (TS1M3), generates Microsoft Excel workbooks in native XLSX format. This destination generates multiple worksheets per workbook with each output object (e.g., a table or graphic) the destination encounters by default. The ODS Excel destination is also flexible, enabling you to vertically control the worksheet and place output objects wherever you want. This blog demonstrates the destination’s flexibility and how you can modify its default behavior by using the ODS EXCEL statement's SHEET_INTERVAL= option.

Adding tables and graphics on the same Microsoft Excel worksheet

By default, the ODS Excel destination adds a new worksheet for each table and graphic. However, at times, you might not want to use this default behavior. If you want more control over this, the SHEET_INTERVAL= ODS Excel option determines when a new worksheet is created. Valid values for the SHEET_INTERVAL= option include:

  • TABLE (the default value) - new sheet for each table in output
  • NONE - keep the output that follows on the current sheet
  • PAGE - new sheet for each page of SAS output
  • PROC - new sheet beginning at the PROC step boundary
  • BYGROUP - new sheet for each BY group of output
  • NOW - begin a new sheet immediately

The value NOW, new for SAS 9.4M5 (TS1M5), triggers the creation of a new worksheet after the destination encounters the next output object.
As an example of opting not to use the default behavior, consider a case where you have a CONTENTS procedure without any options. This procedure generates three separate worksheets with the data-set attributes, the engine host information, and the variable list.

The following table shows the default output that you receive with three individual worksheets:

However, if you want to place all three objects on a single worksheet, you can do that by setting the option SHEET_INTERVAL="NONE". The option setting SHEET_INTERVAL="PROC" could also be used in this example which would create a new worksheet only when a new procedure is encountered.
The following example illustrates how to use this option to include all your output on the same worksheet:

ods excel file="c:\test.xlsx" options sheet_interval="none");
proc contents data=sashelp.class;
run; 
ods excel close;

Output

Adding text and tables to a new worksheet

Two of the most popular ways to add text on worksheets are to use either the ODS TEXT= statement or the ODSTEXT procedure with the Excel destination. The following example adds text to a worksheet by using the ODS TEXT= statement. You include this statement before each PRINT procedure in this example:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males");
ods text="Data for Male Patients";
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Notice that the first text string appears at the top of the first worksheet as expected. However, the text from the second ODS TEXT= statement appears at the bottom of this same page rather than at the top of the next worksheet containing the related data table. This behavior illustrates that the ODS TEXT= option is not very flexible. There is no good way to solve this issue.

However, you can use the SAS 9.4 ODSTEXT procedure in combination with the SHEET_INTERVAL= option to move the text string to the appropriate worksheet.
The following example uses PROC ODSTEXT and the SHEET_INTERVAL= option to move the text string "Statistics for Male Patients" to the top of the second worksheet:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males" sheet_interval="now");
ods excel options(sheet_name="Males" sheet_interval="none");
 
proc odstext;
   p "Data for Male Patients";
run;
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Adding multiple tables or graphs on the same worksheet

This final example demonstrates how you can use the SHEET_INTERVAL= option to add multiple tables and graphics to the same Excel worksheet. First, we use the SHEET_INTERVAL="NONE" option in the first ODS EXCEL statement to place the first table and graph on the same worksheet. Then, the SHEET_INTERVAL="NOW" option is included in the second ODS EXCEL statement option to create a second worksheet and write the second table and graph to that worksheet:

ods graphics / height=2.5in width=3.5in;
ods excel file="c:\scratch\test.xlsx" options(sheet_interval="none");
 
proc print data=sashelp.class(where=(sex="F"));
run;
 
proc sgplot data=sashelp.class(where=(sex="F"));
scatter x=age y=height;
run;
 
ods excel options(sheet_interval="now");
 
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
proc sgplot data=sashelp.class(where=(sex="M"));
scatter x=age y=height;
run;
 
ods excel close;

Output

Learn more

Control the location of tables, graphs, and text with ODS Excel was published on SAS Users.

Control the location of tables, graphs, and text with ODS Excel

 Microsoft Excel, ODS Excel destination, output objects, Problem Solvers, SAS 9.4M3, Tech, xlsx  No Responses »
4月 242019
 
The ODS Excel destination, which became a production feature in SAS 9.4M3 (TS1M3), generates Microsoft Excel workbooks in native XLSX format. This destination generates multiple worksheets per workbook with each output object (e.g., a table or graphic) the destination encounters by default. The ODS Excel destination is also flexible, enabling you to vertically control the worksheet and place output objects wherever you want. This blog demonstrates the destination’s flexibility and how you can modify its default behavior by using the ODS EXCEL statement's SHEET_INTERVAL= option.

Adding tables and graphics on the same Microsoft Excel worksheet

By default, the ODS Excel destination adds a new worksheet for each table and graphic. However, at times, you might not want to use this default behavior. If you want more control over this, the SHEET_INTERVAL= ODS Excel option determines when a new worksheet is created. Valid values for the SHEET_INTERVAL= option include:

  • TABLE (the default value) - new sheet for each table in output
  • NONE - keep the output that follows on the current sheet
  • PAGE - new sheet for each page of SAS output
  • PROC - new sheet beginning at the PROC step boundary
  • BYGROUP - new sheet for each BY group of output
  • NOW - begin a new sheet immediately

The value NOW, new for SAS 9.4M5 (TS1M5), triggers the creation of a new worksheet after the destination encounters the next output object.
As an example of opting not to use the default behavior, consider a case where you have a CONTENTS procedure without any options. This procedure generates three separate worksheets with the data-set attributes, the engine host information, and the variable list.

The following table shows the default output that you receive with three individual worksheets:

However, if you want to place all three objects on a single worksheet, you can do that by setting the option SHEET_INTERVAL="NONE". The option setting SHEET_INTERVAL="PROC" could also be used in this example which would create a new worksheet only when a new procedure is encountered.
The following example illustrates how to use this option to include all your output on the same worksheet:

ods excel file="c:\test.xlsx" options sheet_interval="none");
proc contents data=sashelp.class;
run; 
ods excel close;

Output

Adding text and tables to a new worksheet

Two of the most popular ways to add text on worksheets are to use either the ODS TEXT= statement or the ODSTEXT procedure with the Excel destination. The following example adds text to a worksheet by using the ODS TEXT= statement. You include this statement before each PRINT procedure in this example:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males");
ods text="Data for Male Patients";
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Notice that the first text string appears at the top of the first worksheet as expected. However, the text from the second ODS TEXT= statement appears at the bottom of this same page rather than at the top of the next worksheet containing the related data table. This behavior illustrates that the ODS TEXT= option is not very flexible. There is no good way to solve this issue.

However, you can use the SAS 9.4 ODSTEXT procedure in combination with the SHEET_INTERVAL= option to move the text string to the appropriate worksheet.
The following example uses PROC ODSTEXT and the SHEET_INTERVAL= option to move the text string "Statistics for Male Patients" to the top of the second worksheet:

ods excel file="c:\temp\test.xlsx";
ods excel options(sheet_name="Females");
 
ods text="Data for Female Patients";
proc print data=sashelp.class(where=(sex="F"));
run;
 
ods excel options(sheet_name="Males" sheet_interval="now");
ods excel options(sheet_name="Males" sheet_interval="none");
 
proc odstext;
   p "Data for Male Patients";
run;
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
ods excel close;

Output

Adding multiple tables or graphs on the same worksheet

This final example demonstrates how you can use the SHEET_INTERVAL= option to add multiple tables and graphics to the same Excel worksheet. First, we use the SHEET_INTERVAL="NONE" option in the first ODS EXCEL statement to place the first table and graph on the same worksheet. Then, the SHEET_INTERVAL="NOW" option is included in the second ODS EXCEL statement option to create a second worksheet and write the second table and graph to that worksheet:

ods graphics / height=2.5in width=3.5in;
ods excel file="c:\scratch\test.xlsx" options(sheet_interval="none");
 
proc print data=sashelp.class(where=(sex="F"));
run;
 
proc sgplot data=sashelp.class(where=(sex="F"));
scatter x=age y=height;
run;
 
ods excel options(sheet_interval="now");
 
 
proc print data=sashelp.class(where=(sex="M"));
run;
 
proc sgplot data=sashelp.class(where=(sex="M"));
scatter x=age y=height;
run;
 
ods excel close;

Output

Learn more

Control the location of tables, graphs, and text with ODS Excel was published on SAS Users.