SAS programmers

232017
 

SAS® users have an easy and convenient way to quickly obtain useful information (referred to as metadata) about their SAS session with a number of read-only SAS DICTIONARY tables or SASHELP views. At any time during a SAS session, information about currently defined system options, libnames, tables, columns and their [...]

The post Exploring the content of the DICTIONARIES table and VSVIEW SASHELP view appeared first on SAS Learning Post.

222017
 

Editor's note: The following post is from Scott Leslie, PhD, Manager of Advanced Analytics for MedImpact Healthcare Systems, Inc. Scott will be one of the Code Doctors at SAS Global Forum 2017.

Learn more about Scott.

VISIT THE CODE CLINIC AT SASGF 2017

$0 copay, no deductible.  No waiting rooms, no outdated magazines. What kind of doctor’s office is this? While we might not be able to help with that nasty cough, SAS Code Doctors are here to help – when it comes to your SAS code, that is.

Yes, the Code Doctors return to SAS Global Forum 2017! This year the Code Clinic will have over 20 SAS experts on-call to answer your questions on syntax, SAS Solutions, best practices and concepts across a broad range of SAS topics/applications, including Base SAS, macros, report writing, ODS, SQL, SAS Enterprise Guide, statistics, and more. It’s a fantastic opportunity to review code, ask questions, develop and brainstorm with peers who have decades of experience using SAS. Bring your code on paper, a flash drive, or a laptop. We’ll have 3-4 laptops with several versions of SAS software installed: 9.1.3 to 9.4 and EG 4.1 to 7.1. And if we can’t answer your coding question at the clinic, we can easily refer you to a specialist, namely the SAS R&D section of the Quad.

So, take advantage of this personalized learning experience in the Lower Quad area of the conference. Clinic office hours are:

  • Monday 4/3, 10:00 am - 3:30 pm
  • Tuesday 4/3, 9:30 am – 2:00 pm and 3:30 pm – 6:00 pm

Here’s the detailed schedule of our all-star code doctor lineup. If you haven’t heard of these names yet, you have now...

/*Just by reading this blog…*/.

 

About Scott Leslie

Scott Leslie, PhD, is Manager of Advanced Analytics for MedImpact Healthcare Systems, Inc. with over15 years of SAS® experience in the pharmacy benefits and medical management field. His SAS knowledge areas include SAS/STAT, Enterprise Guide, and Visual Analytics. Scott presents at local, regional and international SAS user group conferences as well at various clinical and scientific conferences. He is a former executive committee member of the Western Users of SAS Software (WUSS) and contributes to the San Diego SAS Users’ Group (SANDS).

Visit the code clinic at SAS Global Forum was published on SAS Users.

202017
 

accessible ODS results with SASLet’s look at the term “accessible” and how it relates to the SAS world. Accessible output is output that can be read by a screen reader to someone with low or no vision, visualized by someone with low vision or color blindness, or navigated by someone with limited mobility. In January of 2017, the United States Access Board published a final rule that documents federal standards and guidelines for accessibility compliance specific to information and communication technology (ICT). The new standards and guidelines update the Section 508 law that was most recently amended in 1998, and adopt many of the Web Content Accessibility Guidelines (WCAG) 2.0 standards. Here’s a comparison document: Comparison Table of WCAG 2.0 to Existing 508 Standards. The final rule (as a parent, I appreciate this rule name!) is also known as the “508 refresh”.

To help SAS US customers comply with the visual-accessibility regulations outlined by the final rule, ODS developers are providing SAS programmers with the ability to create accessible results to present their SAS data. In SAS® 9.4 TS1M4 (9.4M4), there are three great improvements that I would like to highlight:

  • ACCESSIBLE_GRAPH option (preproduction) in the ODS HTML5 statement
  • SAS® Graphics Accelerator
  • ACCESSIBLE option (preproduction) in the ODS PDF statement

Note about “Preproduction” Status: ACCESSIBLE and ACCESSIBLE_GRAPH

Why are the ACCESSIBLE (in the ODS PDF FILE= statement) and ACCESSIBLE_GRAPH (in the ODS HTML5 FILE=statement) options preproduction? By setting the status as preproduction, the development team has greater flexibility to make changes to the syntax and underlying architecture. The development team has worked hard to provide these new features, and is very eager to hear feedback from the SAS programming community. They also encourage feedback from the compliance teams that work for those of you who are striving to make your SAS results accessible. Please request and install SAS 9.4M4 here, and start using these new features to generate your current results in the new formats. Ask your compliance team to assess the output, and let us know (accessibility@sas.com) how close we are to making your files compliant with the final rule.

ODS HTML5

The ODS HTML5 destination, which was introduced in SAS® 9.4, creates the most accessible output in SAS for consumption of tables and graphs on the web. This destination creates SVG graphs from ODS GRAPHICS results. SVG graphics scale when zoomed, which maintains the visual integrity of the image. To ensure that the results comply with the maximum number of WCAG standards, use one of the following styles:

  • DAISY (recommended)
  • VADARK
  • HIGHCONTRAST
  • JOURNAL2

In most cases, these styles provide a high level of contrast in graphics output and tabular output.[1]
When you use an accessibility checker such as the open-source accessibility testing tool aXe, you can see how the HTML5 destination compares to the HTML4 destination (the default destination in SAS® Foundation). Here is a comparison of a simple PRINT procedure step. Using this code, I generate two HTML files, html4.html and html5.html:

ods html file="html4.html" path="c:temp" ;
ods html5 file="html5.html" path="c:temp" style=daisy;
 
proc print data=sashelp.cars(obs=5);
var type origin;
run;
 
ods _all_ close;

 

An aXe analysis in Mozilla Firefox finds the following violations in the html4.html file:

Here is the analysis of the html5.html file:

Your success might vary with more complex procedures. The final paragraph of the blog post describes how to offer feedback after you test your code in the HTML5 destination using SAS 9.4M4.

SAS® Graphics Accelerator

The addition of the ACCESSIBLE_GRAPH preproduction option to the ODS HTML5 statement adds accessibility metadata (tags) around ODS GRAPHICS images routed to the HTML5 destination[2]. This metadata provides the ability to have bar charts, time series plots, heat maps, line charts, scatter plots, and histograms consumed by an exciting new add-in available for the Google Chrome and (coming soon) Firefox browsers: SAS GRAPHICS Accelerator. SAS Graphics Accelerator provides the following capabilities:

  • The interactive exploration of supported graphics using sound
  • The ability to download data in tabular format to a CSV file
  • Customization of visual and auditory settings for alternative presentations

Pay attention to the SAS Graphics Accelerator web page because improvements and features are being offered on a regular basis!

ODS PDF ACCESSIBLE

The web is “where it’s at” for most consumers of your organization’s information. However, many sites need PDF files for results requiring a longer storage time. Prior to SAS 9.4M4, using a screen reader with PDF files created by ODS did not work because the PDF files created by ODS are not “tagged.” Tags in a PDF file are not visible in Adobe Reader when the file is opened. But, when a PDF file is tagged, the file contains underlying metadata to facilitate screen readers verbalizing the results. Here’s an example of the same PROC PRINT step above written to two different PDF files using SAS 9.4M4. I created the tagged.pdf file using the ACCESSIBLE preproduction destination option, so the file includes tags, making it accessible using assistive technology.

ods pdf file="c:tempuntagged_default.pdf";
ods pdf (id=a) file="c:temptagged.pdf" accessible;
 
proc print data=sashelp.cars(obs=5);
var type origin;
run;
 
ods _all_ close;

 

 

To determine whether a PDF file is tagged, open the file and select View ► Show/Hide ► Navigation Panes ► Tags. For example, I checked the untagged_default.pdf file and saw the following, which means that this file is not useful to a screen reader:

Let’s compare the results from the tagged.pdf file:

A screen reader uses the HTML-like markup shown above to verbalize the file to someone with low or no vision.

Adobe Acrobat Pro has built-in accessibility checkers that enable us to examine the degree of accessibility of our files. You can display this setting by selecting View ► Tools ► Accessibility. A full discussion of the Adobe compliance-check features is outside the scope of this article. But, an initial examination of the tagged.pdf file shows that there are many accessible features included in the file, and that two of the features need a manual check:

Check with staff who are well-versed in compliance at your organization, and let us know if our files meet your standards.

I want to see sample code, and hear more! How do I get access to SAS 9.4M4 and more information about these new features?

Your SAS installation representative can order SAS 9.4M4 using the information on this page, Request a Maintenance Release. If you want to get a preview of SAS 9.4M4 to learn it before your site gets it, you can use SAS® University Edition (www.sas.com/universityedition).

Read these upcoming papers (available in April 2017) for code samples. And, if you are attending SAS Global Forum 2017, plan to attend the following presentations:

Here are links to the documentation and a previously published SAS Global Forum paper on the topic:

How did we do?

We welcome feedback regarding the results that you are generating with SAS 9.4M4, and look forward to offering the ODS statement options in production status with improved features and support for more procedures. Please send feedback to accessibility@sas.com.

 

[1] See “ODS HTML5 Statement Options Related to Accessibility” in Creating Accessible SAS 9.4 Output Using ODS and ODS Graphics for more information

 

Create accessible ODS results with SAS or "Why you should be running SAS 9.4m4!" was published on SAS Users.

182017
 

Editor’s note: This is the third in a series of articles to help current SAS programmers add SAS Viya to their analytics skillset. In this post, Advisory Solutions Architect Steven Sober explores how to accomplish distributed data management using SAS Viya. Read additional posts in the series.

In my last article I explained how SAS programmers can execute distributed DATA Step, PROC DS2, PROC FEDSQL and PROC TRANSPOSE in SAS Viya’s Cloud Analytic Services (CAS) which speeds up the process of staging data for analytics, visualizations and reporting. In this article we will explore how open source programmers can leverage the same SAS coding techniques while remaining in their comfort zone.

For this post, I will utilize Jupyter Notebook to run the Python script that is leveraging the same code we used in part one of this series.

Importing Package and Starting CAS

First, we import the SAS Scripting Wrapper for Analytics Transfer (SWAT) package, which is the Python client to SAS Cloud Analytic Services (CAS). To down load the SWAT package, use this url: https://github.com/sassoftware/python-swat.

Let’s review the cell “In [16]”:

1.  Import SWAT

a.  Required statement, this loads the SWAT package into our Python client

2.  s = swat.CAS("viya.host.com", port#, "userid", "password")

a.  Required statement, for our example we will use “s” in our dot notation syntax to send our statements to CAS for processing. “s” is end-user definable (i.e. I could have used “steve =” instead of “s =”).

b.  Viya.host.com is the host name of your SAS Viya platform

c.  Port#

i.  Port number used to communicate with CAS

d.  userid

i.  Your user id for the SAS Viya platform

e.  Password

i.  Your password for your userid

3.  indata_dir = "/opt/sasinside/DemoData"

a.  Creating a variable call “indata_dir”. This is a directory on the SAS Viya platform where the source data for our examples is located.

4.  indata     = "cars"

a.  Creating a variable call “indata” which contains the name of the source table we will load into CAS

Reviewing cell “Out[16]” we see the information that CAS returns to our client when we connect successfully.

Loading our Source Table and CAS Action Sets

In order to load data into CAS we first need to create a dataSource={"srcType":"PATH"},
path = indata_dir)

a.  To send statements to CAS we use dot notation syntax where:

a.  s

i.  The CAS session that we established in cell “in[16]”

b.  table

i.  CAS action set

c.  addCaslib

i.  Action set’s action

d.  name

i.  Specifies the name of the caslib to add.

e.  dataSource

i.  Specifies the data source type and type-specific parameters.

f.  path

i.  Specifies data source-specific information. For PATH and HDFS, this is a file system path. In our example we are referencing the path using the variable “indata_dir” that we established in cell “In[16]”.

casOut={"caslib":"casuser", "name":"cars",
"replace":"True"},

)

a.  As we learned s. is our connection to CAS and “table.” is the CAS action set while “Table” is the action set’s action.

a.  path=

i.  Specifies the file, directory or table name. In our example this is the physical name of the SAS data set being loaded into CAS.

b. casOut=

i.  The CAS library we established in cell “In[17]” using the “addCaslib” action.

1  caslib.casuser

a.  “caslib” - is a reserved word and is use to reference all CAS libraries
b.  “casuser” - is the CAS library we will use in our examples
c.  “name”  - is the CAS table name
d.  “replace” - provides us an option to replace the CAS table if it already exists.

Reviewing cell “Out[17]” we see the information that CAS returns to our client when we successfully load a table into CAS.

Click here for information on the loadActionSet action.

DATA Step

We are now ready to continue by running DATA Step, PROC DS2, PROC FEDSQL and PROC TRANSPOSE via our python script.

Now that we understand the dot notation syntax used to send statements to CAS, it become extremely simple to leverage the same code our SAS programmers are using.

Reviewing cell “In[19]” we notice we are using the CAS action set “dataStep” and it’s action “runCode”.  Notice between the (“”” and  “””) we have the same DATA Step code we reviewed in part one of this series. By reviewing cell “Out19]” we can review the information CAS sent back providing information on the source (casuser.cars) and target (casuser.cars_data_step) tables used in our DATA Step.

With DS2 we utilize the CAS action set “ds2” with its action “runDS2.” In reviewing cell “In[23]” we do notice a slight difference in our code. There is no “PROC DS2” prior to the “thread mythread / overwrite = yes;” statement. With the DS2 action set we simply define our DS2 THREAD program and follow that with our DS2 DATA program. Notice in the DS2 DATA program we declare the DS2 THREAD that we just created.

Review the NOTE statements: prior to “Out[23]” These statements validate the DS2 THREAD and DATA programs executed in CAS.

With FedSQL we use the CAS action set “fedsql’ with its action “execDirect.” The “query=” parameter is where we place our FedSQL statements. By reviewing the NOTE statements we can validate our FedSQL ran successfully.

With TRANSPOSE we use the CAS action set “transpose” with its action “transpose.” The syntax is different for PROC TRANSPOSE, but it is very straight forward on mapping out the parameters to accomplish the transpose you need for your analytics, visualizations and reports.

Collaborative distributed data management with open source was published on SAS Users.

092017
 

Several months ago, I posted a blog about calculating moving averages for a measure in the Visual Analytics Designer. Soon after that, I was asked about calculating not only the average, but also the standard deviation over a period of months, when the data might consist of one or more repeated values of a measure for each month of a series of N months.  For the example of N=20 months, we might want to view the average and standard deviation over the last n months, where n is any number between 3 and 20.

The example report shown below allows the user to type in a number, n, between 3 and 20, to display a report consisting of the amount values for past n months, the amount values for Current Month Amt-Previous, the Avg over the last n months, the Standard Deviation over the last n months, and the absolute value of the (Current Month Amt – Previous Month Amt), divided by the Standard Deviation over the last n months. A Display rule is applied to the final Abs column, showing Green for a value less than 1 and red for a value greater than or equal to 1.

The data used in this example had multiple Amount values for each month, so we first used the Visual Data Builder to create a SUM aggregation for Amount for each unique Date value.  This step gives more flexibility in using the amount value for aggregations in the designer.

When the modified data source is initially added to the report, it contains only the Category data item Month, with a format of MMYYYY, and the measure Amount Sum for Month.

The data will be displayed in a list table. The first columns added to the table will be Month, displayed with a MMYYYY format, and Amount Sum for Month.

Specify the properties for the list table as below:

Since we want to display the last n months, we create a new calculated data item, Numeric Date, calculated as below, using the TREATAS operator on the Month data item:

Then we create the Current Month Amt-Previous aggregated measure using the RelativePeriod date operator:

Next, create the Avg over all displayed months aggregated measure as below:

Then, create the Std.Dev. over all displayed months aggregated measure as shown below:

Create the Abs (Current-Previous/StdDev) as shown below:

Create a numeric parameter, Number of Months, as shown, with minimum value of 3 (smallest value that a standard deviation will make sense) and maximum value of 20 (the number of months in our data). You can let the default (Current value) value be any value that you choose:

For the List Table, create a Rank, as shown below. Note that we are creating the rank on the Numeric Date (not the Month data item), and rather than a specific value for count, we are going to use the value of the parameter, Number of Months.

Create a text input object that enables the user to type in a ‘number of months’ between 3 and 20.

Associate the Parameter with the Text input object:

If you wish, you can add display rules to sound an alarm whenever there is an alarming month-to-month difference in comparison to the standard deviation for the months.

So the final result of all of the above is this report, which points out month-to-month differences, which might deserve further concern or investigation. Note that the Numeric Date value is included below just to enable you to see what those values look like—you likely would not want to include that calculated data item in your report.

Calculating standard deviation of a measure in Visual Analytics Designer was published on SAS Users.

092017
 

Editor’s note: This is the first in a series of posts to help current SAS programmers add SAS Viya to their analytics skillset. In this post, SAS instructors Stacey Syphus and Marc Huber introduce you to the new Transitioning from Programming in SAS 9 to SAS Viya video library, designed to show SAS programmers [...]

The post Transitioning from programming in SAS 9 to SAS Viya appeared first on SAS Learning Post.

212017
 

The SAS® Output Delivery System provides the ability to generate output in various destination formats (for example, HTML, PDF, and Excel). One of the more recent destinations, ODS Excel, became production in the third maintenance release for SAS 9.4 (TS1M3). This destination enables you to generated native Microsoft Excel formatted files, and it provides the capability to generated worksheets that include graphics, tables, and text. If you generate spreadsheets, then the ODS Excel destination (also known as just the Excel destination) might be just the tool you're looking for to help enhance your worksheets.

This post begins by discussing the following Excel destination options that are useful for enhancing the appearance of worksheets:

  • START_AT=
  • FROZEN_HEADERS= and FROZEN_ROWHEADERS=
  • AUTOFILTER=
  • SHEET_NAME=
  • ROW_REPEAT=
  • EMBEDDED_TITLES=

The discussion also covers techniques for adding images to a worksheet as well as a tip for successfully navigating worksheets. Finally, the discussion offers tips for moving to the use of the Excel destination if you currently use one of the older ODS destinations (for example, the ExcelXP destination) and information about suggested hot fixes.

Using Excel Destination Options to Enhance the Appearance of Your Microsoft Excel Worksheet

There are certain ODS Excel destination options that you could conceivably add to any program that would make it easier for your users to navigate your worksheets.

These options include the following:

  • START_AT= option
  • FROZEN_HEADERS= and FROZEN_ROWHEADERS= options
  • AUTOFILTER= option
  • EMBEDDED_TITLE= option

The following example uses all of the options described above. In this example, filters are added only to the character fields.

ods excel file="c:temp.xlsx" options(start_at="3,3"
frozen_headers="5"
frozen_rowheaders="3"
autofilter="1-5"
sheet_name="Sales Report"
row_repeat="2"
embedded_titles="yes");
 
proc print data=sashelp.orsales; 
title "Sales Report for the Year 1999";
run;
 
ods excel close;

In This Example

  • The START_AT= option enables you to select the placement of the initial table or graph on the worksheet. In Microsoft Excel, by default, a table or graph begins in position A1. However, pinning the table or graph in that position does not always provide the best visual presentation.
  • The FROZEN_ HEADERS= option locks the table header in your table while the FROZEN_ROWHEADERS= option locks row headers. Both of these options lock your headers and row headers so that they remain visible as you scroll through the values in the table rows and columns.
  • The AUTOFILTER= option enables you to add filters to tables headers so that you can filter based on the value of a particular column.
  • The SHEET_NAME= option enables you to add more meaningful text to the worksheet tab.
  • The ROW_REPEAT= option makes your report more readable by repeating the rows that you specify in the option. If this report is ever printed, specifying the rows to repeat, in this case the column headers would allow for easy filtering of the data.
  • The EMBEDDED_TITLE= option specifies that the title that is specified in the TITLE statement should be displayed.

Output

Using the Excel Destination to Add and Update Images

Microsoft Excel is widely known and used for its ability to manipulate numbers. But if you want to go beyond just numbers, you can make your worksheets stand out by adding visual elements such as images and logos.

Graphic images that you generate with ODS Graphics and SAS/GRAPH® software (in SAS 9.4 TS1M3) are easy to add to a worksheet by using the Excel destination. However, the addition and placement of some images (for example, a logo) can take a bit more work. The only fully supported method for adding images other than graphics is to add an image as a background image.

The next sections discuss how you can add various types of images to your worksheet.

Adding Background Images

You can add images to the background of a worksheet by using either the TEMPLATE procedure or cascading style sheets. With PROC TEMPLATE, you add background images by using the BACKGROUNDIMAGE= attribute within the BODY style element. You also must specify the BACKGROUND=_UNDEF attribute to remove the background color. With a cascading style sheet, you use the BACKGROUND-IMAGE style property.

The following example illustrates how to add a background image using PROC TEMPLATE:

proc template; 
define style styles.background;
parent=styles.excel;
class body / background=_undef_
backgroundimage="c:background.jpg.";
end;
run;
 
ods excel file="c:temp.xlsx"
options(embedded_titles="yes" start_at="5,5"
sheet_name= "Sheet1") style=styles.background;
 
proc report data=sashelp.prdsale spanrows;
title "Expense Report Year 2016";
column country product actual predict; 
define country / group;
define product / group;
rbreak after / summarize;
run;
 
ods excel close;

In This Example

  • PROC TEMPLATE uses the BACKGROUNDIMAGE= attribute within the BODY style element of the CLASS statement to include the image.
  • The BACKGROUND=_UNDEF attribute removes the background color.

As you can see in the following output, Excel repeats (or, tiles) images that are used as a background.  Excel repeats the image across the width of the worksheet.

Output

But this method of tiling might not be what you want. For example, you might want your image to cover the entire worksheet. To prevent the background image from being tiled, you can insert the image into an image editor (for example, Microsoft Paint) and enlarge the background image so that it covers the full page. You can also create a canvas (that is, a page) in the image editor and then add your background image to the canvas and save it. The Excel destination does not support transparency, a property in where the background image is visible through an image. However, you can use PROC TEMPLATE to simulate transparency by removing the background colors of the various cells. When you use any of the methods described above, your output includes an image that covers the full page.

The following example uses the PROC TEMPLATE method to create the background image and remove the background colors of the cells:

proc template;
define style styles.background;
parent=styles.excel;
class body / background=_undef_ backgroundimage="C:background_large.jpg";
class header, rowheader, data / color=white 
borderwidth=5pt
bordercolor=white
borderstyle=solid
background=_undef_;
end;
run;
 
ods excel file="c:temp.xlsx" options(embedded_titles="yes"
start_at="5,5"
sheet_name="Sheet1") 
style=styles.background;
 
proc report data=sashelp.prdsale spanrows;
title "Expense Report Year 2016";
column country product actual predict;
define country / group;
define product / group;
rbreak after / summarize;
run;
 
ods excel close;

In This Example

  • First, the image was included in Microsoft Paint to enlarge it.
  • Then, PROC TEMPLATE uses the BACKGROUNDIMAGE= attribute within the BODY style element of the CLASS statement to include the enlarged image.

Output

Adding External Images to the Worksheet

Currently, the Excel destination does not support adding external images on a per-cell basis.

However, you can add external images (for example, a company logo) using either of the following methods:

You can accomplish this task in the following ways:

  • manually add an image using an image editor
  • use the GSLIDE procedure with the GOPTIONS statement
  • use the %ENHANCE macro.

Adding an Image with an Image Editor

Using an image editor such as Microsoft Paint, you can place an image (for example, a logo) wherever you want it on the worksheet. In the following display, the image is brought into the Paint application and moved to the top left of a canvas.

After you save this image, you can include it in an Excel worksheet as a background image using the BACKGROUNDIMAGE= attribute, which displays the logo without repeating it.

proc template; 
define style styles.background_kogo;
parent=styles.excel;
class body / background=_undef_
backgroundimage="c:tempbackground_logo";
end;
run;
 
ods excel file="c:temp.xlsx" style=styles.background_logo;
proc print data=sashelp.class;
run;
ods excel close;

Output

Adding an Image Using the GOPTIONS Statement with the GSLIDE Procedure

You can also use the GOPTIONS statement and PROC GSLIDE procedure with the Excel destination to add a logo on the worksheet. This method requires SAS/GRAPH software to be licensed and installed.

To add a background image to the graph display area of PROC GSLIDE output, specify the IBACK= option in the GOPTIONS statement, as shown in the following example:

ods excel file="c:temp.xlsx" options(sheet_interval="none");
 
goptions iback="c:sas.png" imagestyle=fit vsize=1in hsize=2in;
 
proc gslide;
run;
 
proc report data=sashelp.class;
run;
 
ods excel close;

 

In This Example

  • The GOPTIONS statement with the IBACK= option adds a background image to the graph display area.
  • The IMAGESTYLE=FIT option keeps the image from repeating (tiling).
  • The VSIZE= and HSIZE= options modify the size of the image.
  • The Excel destination suboption SHEET_INTERVAL="NONE" specifies that the image and report output are to be added to the same worksheet.

Output

Adding an Image Using the %EXCEL_ENHANCE Macro

The %EXCEL_ENHANCE is a downloadable macro that enables you to place images on a worksheet in an exact location by using a macro parameter. The macro creates VBScript code that inserts your image in a specified location on the worksheet during post-processing.

The following example uses the %EXCEL_ENHANCE macro to add an image to a workbook.

Note: This method is limited to Microsoft Windows operating environments.

%include "C:excel_macro.sas";
%excel_enhance(open_workbook=c:temp.xlsx, 
insert_image=%str(c:SAS.png#sheet1!a1,
c:canada.jpg#sheet1!b5,
c:germany.jpg#sheet1!b10,
c:usa.jpg#sheet1!b15),
create_workbook=c:temp_update.xlsx,
file_format=xlsx);

In This Example

  • The %INCLUDE statement includes the %EXCEL_ENHANCE macro into your program.
  • The %EXCEL_ENHANCE macro uses the INSERT_IMAGE= parameter to insert an image into the worksheet at a specified location. You can also specify multiple images, but they must be separated by commas.

The INSERT_IMAGE= option uses the following syntax in the %STR macro function to pass the image.

image-location #sheet-name ! sheet-position
  • The OPEN_WORKBOOK= parameter specifies the location of the workbook in which you want to add an image.
  • The CREATE_WORKBOOK= parameter creates a new workbook that includes your changes.
  • The FILE_FORMAT= parameter enables you to specify the extension or format for the files that are created.

Output

Navigating a Microsoft Excel Workbook

When you generate an Excel workbook with the Excel destination, the best way to navigate the workbook is by creating a table of contents. You can create a table of contents by using the Excel destination's CONTENTS= option. You can also use the PROC ODSLABEL statement to modify the table-of-contents text that is generated by the procedure. In the following example, that text (The Print Procedure) is generated by the PRINT procedure.

ods excel file="c:temp.xls" options(embedded_titles="yes"
 contents="yes");
 
ods proclabel= "Detail Report of Males";
 
proc print data=sashelp.class(where=(sex="M"));
title link="#'The Table of Contents'!a1"  "Return to TOC";
run;
 
ods proclabel= "Detail Report of Females";
 
proc print data=sashelp.class(where=(sex="F"));
title link="#'The Table of Contents!a1'"  "Return to TOC";
run;
 
ods excel close;

 

In This Example

  • The CONTENTS= option is included in the ODS EXCEL statement to create a table of contents. You can also use the INDEX= suboption (not shown in the example code) to generate an abbreviated output. These options create the first worksheet within a workbook and include an entry to each element that is generated in the workbook.
  • The ODS PROCLABEL statement is used to modify the table-of-contents text that is generated by the procedure name. In this example, the text The Print Procedure (generated by the two PROC PRINT steps) is modified to Detail Report of Males and Detail Report of Females, respectively.
  • You can also modify the secondary link by using the CONTENTS= option in the procedure statements for the PRINT, REPORT, and TABULATE procedures.
  • The LINK= option in the TITLE statement adds a link that returns you to Table of Contents navigation page. You can also use this option in a FOOTNOTE statement. The argument that you specify for the LINK= option is the sheet name for the Table of Contents page. You can also add a link by using the Microsoft Excel hyperlink function in the ODS TEXT= statement using the Excel Hyperlink function.

Output

The output below shows the Table of Contents navigation page.

The next output shows a page in the report that contains the Return to TOC link.

Using the ODS Excel Destination instead of Older Destinations

Currently, you might be using older destinations (for example, the MSOffice2K or the ExcelXP tagsets).  If you decide to move to the ODS Excel destination, you'll notice differences related to style, options, and wrapping between it and other destinations.

  • One difference is that the Excel destination uses the Excel style, which looks similar to the HTMLBlue style. Other destinations use different styles (for example, the ExcelXP tagset uses styles.default.
  • Certain options that are valid with the ExcelXP tagset are not valid in the Excel destination.
  • Another difference that you'll notice right away is how the text is wrapped by the Excel destination. By default, the Excel destination uses an algorithm to try to wrap columns in order to prevent overly wide columns. When text wraps, a hard return is added automatically to the cell (similar to when you either press Alt+ Enter from the keyboard under Windows or you submit a carriage-return line feed [CRLF] character). You can prevent the Excel destination from adding this hard return in wrapping by specifying a width value that is large enough so that text does not wrap. You can also use the Excel destination's new FLOW= suboption, which is available in the fourth maintenance release for SAS 9.4 (TS1M4). This option contains the parameters TABLES, ROWHEADERS, DATA, TEXT, and a range (for example, A1:E20). When you specify the parameter TABLES, that automatically includes the HEADERS, ROWHEADERS, and DATA parameters.

The following example demonstrates how to prevent the Excel destination from automatically generating a hard return for wrapped text in SAS 9.4 TS1M4.

data one;
var1="Product A in Sports";
var2="Product B in Casual";
label var1="Product Group for All Brands in Region 1";
label var2="Product Group for All Brands in Region 2";
run;
 
ods excel file="c:temp.xlsx" options(flow="tables");
 
proc print data=one label;
run;
 
ods excel close;

In This Example

  • The first table shown in the output below is created by default. As a result, the header wraps in the formula bar where the CRLF character is added.
  • The second table in the output is generated with the FLOW="TABLES" suboption (in the ODS EXCEL statement) to prevent the destination from adding the CRLF character to table headers, row headers, and data cells. When you add this option, Microsoft Excel text wrapping is turned on, by default.

Output

Table that is created by default:

Table that is created by including the FLOW="TABLES" suboption in the ODS EXCEL statement:

Hot Fixes for the Excel Destination

If you run SAS 9.4 TS1M3 and you use the ODS Excel destination, see the following SAS Notes for pertinent hot fixes that you should apply:

  • SAS 56878, "The ODS destination for Excel generates grouped worksheets when multiple sheets are produced"
  • SAS Note 57088, "The error 'applied buffer too small for requested data' might be generated when you use the ODS destination for Excel"

Resources

Bessler, Roy. 2015. "The New SAS ODS Excel Destination: A User Review and Demonstration."
Proceedings of the Wisconsin, Illinois SAS Users Group. Milwaukee, WI.

Huff, Gina. 2016. "An 'Excel'lent Journey: Exploring the New ODS EXCEL Statement."
Proceedings of the SAS Global Forum 2016 Conference. Cary, NC: SAS Institute Inc.

Parker, Chevell. 2016. "A Ringside Seat: The ODS Excel Destination versus the ODS ExcelXP Tagset."
Proceedings of the SAS Global Forum 2016 Conference. Cary, NC: SAS Institute Inc.

Tips for using the ODS Excel Destination was published on SAS Users.

102017
 

Small matters matter. Imagine saving (or spending wisely) just 1 second of your time every hour. One measly second! During your lifespan you would save or spend wisely (1 sec-an-hour * 24 hours-a-day * 365 days-a-year x 100 years) / (3600 seconds-an-hour * 24 hours-a-day) = 10 days, a whole two week vacation!

While truncation vs rounding may seem to be insignificant in a given instance, the cumulative effect of either could be truly enormous, whether it’s truncation vs rounding of decimal numbers or of the SAS time values presented below.

From my prior post Truncating decimal numbers in SAS without rounding, we know that SAS formats such as w.d, DOLLARw.d, and COMMAw.d do not truncate decimal numbers, but rather round them.

However, SAS time value formats are somewhat different. Let’s take a look.

Suppose we have a SAS time value of '09:35:57't. As a reminder, a SAS time value is a value representing the number of seconds since midnight of the current day. SAS time values are between 0 and 86400.

TIMEw.d Format

Let’s apply the TIMEw.d format to our time value and see what it does.

If you run the following SAS code:

data _null_;
	t = '09:35:57't;
	put t= time5.;
	put t= time2.;
run;

you will get in the SAS log:

t=9:35
t=9

which means that this format does truncate both seconds and minutes. Conversely, if rounding were taking place we would have gotten:

t=9:36
t=10

HHMMw.d Format

Let’s run the same SAS code with HHMMw.d format:

data _null_;
	t = '09:35:57't;
	put t= hhmm5.;
	put t= hhmm2.;
run;

SAS log will show:

t=9:36
t=9

What does that mean? It means that HHMMw.d format rounds seconds (in case of truncating I would expect to get t=9:35), but truncates minutes (in case of rounding I would expect to get t=10, as 35 minutes are closer to 10 than to 9). A bit inconsistent, at least for our purposes.

Truncating SAS time values

This little research above shows that out of the two formats, TIMEw.d and HHMMw.d, it is perfectly fine to use the TIMEw.d format for the purpose of SAS time value truncation, for both minutes and seconds.

Regardless of the format used, you can also truncate your time value computationally, before applying a format, by subtracting from that value a remainder of division of that value by 60 (for seconds truncation) or by 3600 (for minutes truncation). For example, the following code:

data _null_;
	t = '09:35:57't;
	t_m = t - mod(t,60);
	t_h = t - mod(t,3600);
	put t= hhmm5.;
	put t_m= hhmm5.;
	put t_h= hhmm5.;
run;

produces the following SAS log:

t=9:36
t_m=9:35
t_h=9:00

Rounding SAS time values

Now that we’ve learned both the computational method and the TIMEw.d format method of truncation, how do we go about rounding? As long as the format behavior is consistent we can use its truncating functionality to convert it into the rounding functionality. In order to do that we just need to increase the original time value by 60 (seconds) for seconds rounding, and by 3600 (seconds) for minutes rounding. Truncation of that new value is equivalent to rounding of the original value.

Let’s run the following SAS code:

data _null_;
	t = '09:35:57't;
	t_m = t + 60;
	t_h = t + 3600;
	put t_m= time5.;
	put t_h= time2.;
run;

SAS log will show:

t_m=9:36
t_h=10

which means that our original time value '09:35:57't was rounded in both cases – seconds rounding and minutes rounding.

Now you know how to truncate and how to round SAS time values. And don’t forget about your lifetime 2-week vacation opportunity by saving a second every hour; or make it 2 seconds per hour and enjoy the full month off.

tags: SAS Professional Services, SAS Programmers, tips & techniques

Truncating vs rounding SAS time values was published on SAS Users.

242017
 

In a previous blog, Random Sampling: What's Efficient?, I discussed the efficiency of various techniques for selecting a simple random sample from a large SAS dataset.  PROC SURVEYSELECT easily does the job: proc surveyselect data=large out=sample method=srs /* simple random sample */ rate=.01; /* 1% sample rate */ run; Note: […]

The post Stratified random sample: What's efficient? appeared first on SAS Learning Post.

202017
 

SAS/GRAPH 9.4 capabilitiesI remember my grandparents talking about how hard things were for them growing up. They would say, “Things were so bad that we had to walk uphill, both ways, in the freezing snow to get to school.” It was always hard for me to relate to these statements because the school bus picked me up at the end of my driveway. Fast forward to today and people are riding on hoverboards. Through the years, advancement in transportation has made it easier for us to get where we need to be.

SAS/GRAPH® Version 6

The evolution of SAS/GRAPH® is similar. In the earlier days of SAS® software, during Version 5 and 6 of SAS/GRAPH, I understood how difficult it was to create some of the graphs that customers wanted. A customer recently asked whether I could send him the code that produced the graph below, which he found in the SAS/GRAPH® Software: Reference, Volume 1 Version 6 Edition:

sasgraph-9-4-capabilities

In Version 6, the only way to create this graph, referred to as a butterfly chart, was by using SAS/GRAPH and the Annotate facility. The annotation statements added over 60 lines of code to the program.

Below is a snippet of the Version 6 program that created the bars on the left side of the graph.

Click this link to see the entire Version 6 program.

     /* female bars on left */
    %bar(39.8, 10.5, 25.0, 20.0, blue, 0, solid);
    %bar(39.8, 20.71, 15.0, 30.7, green, 0, solid);
    %bar(39.8, 31.42, 10.0, 41.42, red, 0, solid);
    %bar(39.8, 42.14, 32.0, 52.14, blue, 0, solid);
    %bar(39.8, 52.85, 33.0, 62.85, green, 0, solid);
    %bar(39.8, 63.57, 36.0, 73.57, red, 0, solid);
    %bar(39.8, 74.28, 35.0, 84.28, blue, 0, solid);
    %bar(39.8, 85.0, 33.0, 95.0, green, 0, solid);

SAS/GRAPH® 9.4

Fast forward to SAS® 9.4. ODS Graphics and SG procedures have been part of Base SAS® since SAS® 9.3, making it much easier to create high-quality graphs without using additional software. The graph that was previously created using the Annotate facility can now be created using the Graph Template Language (GTL) and the SGRENDER procedure. Here is the program to create the entire graph:

Note: The program below contains numbered annotations that correspond to a discussion below the program. So, if you copy and paste this program into SAS, be sure to delete the annotations before running this code.

data ratio;
  input Age $1-8 type $ Female Male;
datalines;
over 79  A 19 18
70-79    B 15 14
60-69    C 13 12
50-59    A 24 46
40-49    B 26 18
30-39    C 92 61
20-29    A 77 88
under 20 B 42 100
;
run;
 
proc template; 
   define statgraph population; ❶
   begingraph /border=false❷ datacolors=(green blue red); ❸
        entrytitle 'Population Tree' / textattrs=(size=15);❹
   entrytitle 'Distribution of Population by Sex';
   layout lattice ❺/ columns=2 ❻ columnweights=(.55 .45); ❼
      layout overlay ❽/ walldisplay=none y2axisopts=(reverse=true
                         tickvaluehalign=center 
                         display=(tickvalues))  
                         xaxisopts=(displaysecondary=(label) 
                         display=(tickvalues line) reverse=true 
                         labelattrs=(weight=bold));
         barchart category=age response=Female / group=type orient=horizontal 
                                                 yaxis=y2 barlabel=true;
      endlayout;
      layout overlay ❽/ walldisplay=none 
                         yaxisopts=(reverse=true display=none)
                         xaxisopts=(displaysecondary=(label)   
                         display=(tickvalues line)
                         labelattrs=(weight=bold)); 
         barchart category=age response=Male / group=type orient=horizontal 
                                               barlabel=true ;
      endlayout;
   endlayout;
   endgraph;
   end;
 
proc sgrender data=ratio template=population;
run;

As you can see, this program is much simpler than the one created using Version 6 above. Let’s take a closer look at the code.

❶ The TEMPLATE procedure creates a GTL definition called POPULATION with the DEFINE statement.

❷ In the BEGINGRAPH statement, the BORDER=FALSE option turns off the outside border.

❸ The DATACOLORS option defines the colors for the groups.

❹ The ENTRYTITLE statements define the titles for the graph.

❺ A LAYOUT LATTICE block serves as a wrapper for the LAYOUT OVERLAY statements.

❻ The COLUMNS option in the LAYOUT LATTICE block defines the layout of the cells.

❼ Because the graph in the left cell contains the bar values, the COLUMNWEIGHTS option allocates more room for this graph. The values for COLUMNWEIGHTS need to add up to 1.

❽ Two LAYOUT OVERLAY statements define the two cells in this graph.

LAYOUT OVERLAY Code

The two LAYOUT OVERLAY blocks are similar, so we will look closer at only the first one:

layout overlay / walldisplay=none ❶ 
                 y2axisopts=(reverse=true 
                 tickvaluehalign=center display=(tickvalues)) ❷ 
                 xaxisopts=(displaysecondary=(label)display=(tickvalues line)reverse=true ❺ 
                 labelattrs=(weight=bold));
   barchart category=age response=Female ❻/ group=type ❼
                           orient=horizontal ❽ yaxis=y2 ❾ barlabel=true; ❿
endlayout;

❶ The WALLDISPLAY=NONE option turns off the border around the graph.

❷ The REVERSE=TRUE option reverses the Y axis order, TICKVALUEHALIGN=CENTER centers the tick mark values, and the DISPLAY=TICKVALUES option displays only the tick mark values. These options are specified within Y2AXISOPTS.

❸ DISPLAYSECONDARY=(LABEL) displays the X axis label on the X2axis, at the top of the graph.

❹ On the X axis, at the bottom of the graph, the DISPLAY=(TICKVALUES LINE) option displays the tick mark values and the axis line.

❺ The REVERSE=TRUE option also reverses the X axis.

❻ The bar chart contains a bar for each Age. The length of the bars is based on the values of the variable Female with the CATEGORY=AGE and RESPONSE=FEMALE options in the BARCHART statement respectively.

❼ The group variable, TYPE, determines the color of the bars.

❽ The ORIENT=HORIZONTAL option in the BARCHART statement specifies that the bars are horizontal.

❾ YAXIS=Y2 specifies that the values are plotted against the right Y axis, Y2 axis.

❿ The BARLABEL=TRUE option provides labels for the bars.

Here is the graph that is created when you submit this program:

sasgraph-9-4-capabilities02

As this example shows, SAS graphing capabilities have improved over the years just as transportation options have progressed. Be sure to take advantage of these improvements to create helpful visualizations of your data! If you would like to create a similar graph with PROC SGPLOT, refer to Sanjay Matange’s blog post “Butterfly plots.”


Version 6 program

    /* set the graphics environment */
 goptions reset=global gunit=pct border
          ftext=swissb htitle=6 htext=3 dev=png;
 %annomac;
 
    /* create the Annotate data set, POPTREE */
 data poptree;
       /* length and type specification */
    %dclanno;
 
       /* set length of text variable   */
    length text $ 16;
 
 
       /* window percentage for x and y */
    %system(5, 5, 3);
 
       /* draw female axis lines */
    %move(5, 10);
    %draw(40, 10, red, 1, .5);
    %draw(40, 95, red, 1, .5);
 
       /* draw male axis lines */
    %move(56.1, 95);
    %draw(56.1, 10, red, 1, .5);
    %draw(95, 10, red, 1, .5);
 
       /* label categories */
    %label(75.0, 97.0, 'Male', green, 0, 0, 4, swissb, 5);
 
       /* at top */
    %label(25.0, 97.0, 'Female', green, 0, 0, 4, swissb, 5);
    %label(5.0, 5, '100', blue, 0, 0, 4, swissb, 5);
    %label(22.5, 5, ' 50', blue, 0, 0, 4, swissb, 5);
    %label(40.0, 5, ' 00', blue, 0, 0, 4, swissb, 5);
    %label(95.0, 5, '100', blue, 0, 0, 4, swissb, 5);
    %label(75.0, 5, ' 50', blue, 0, 0, 4, swissb, 5);
    %label(56.0, 5, ' 00', blue, 0, 0, 4, swissb, 5);
 
       /* label age */
    %label(48.0, 15.25, 'under 20', blue, 0, 0, 4, swissb, 5);
    %label(48.0, 25.0, '20 - 29', blue, 0, 0, 4, swissb, 5);
    %label(48.0, 36.7, '30 - 39', blue, 0, 0, 4, swissb, 5);
    %label(48.0, 47.4, '40 - 49', blue, 0, 0, 4, swissb, 5);
    %label(48.0, 57.8, '50 - 59', blue, 0, 0, 4, swissb, 5);
    %label(48.0, 68.6, '60 - 69', blue, 0, 0, 4, swissb, 5);
    %label(48.0, 79.3, '70 - 79', blue, 0, 0, 4, swissb, 5);
    %label(48.0, 90.0, 'over 79', blue, 0, 0, 4, swissb, 5);
 
       /* male bars on right */
    %bar(56.2, 10.5, 95.0, 20.0, blue, 0, solid);
    %bar(56.2, 20.71, 90.0, 30.71, green, 0, solid);
    %bar(56.2, 31.42, 80.0, 41.52, red, 0, solid);
    %bar(56.2, 42.14, 62.0, 52.14, blue, 0, solid);
    %bar(56.2, 52.85, 72.0, 62.85, green, 0, solid);
    %bar(56.2, 63.57, 60.0, 73.57, red, 0, solid);
    %bar(56.2, 74.28, 61.0, 84.28, blue, 0, solid);
    %bar(56.2, 85.0, 63.0, 95.0, green, 0, solid);
 
       /* label male bars on right */
    %label(95.0, 20.0, '100', black, 0, 0, 4, swissb, 7);
    %label(90.0, 30.71, '88', black, 0, 0, 4, swissb, 7);
    %label(80.0, 41.52, '61', black, 0, 0, 4, swissb, 7);
    %label(62.0, 52.14, '18', black, 0, 0, 4, swissb, 7);
    %label(72.0, 62.85, '46', black, 0, 0, 4, swissb, 7);
    %label(60.0, 73.57, '12', black, 0, 0, 4, swissb, 7);
    %label(61.0, 84.28, '14', black, 0, 0, 4, swissb, 7);
    %label(62.0, 95.0, '18', black, 0, 0, 4, swissb, 7);
 
       /* female bars on left */
    %bar(39.8, 10.5, 25.0, 20.0, blue, 0, solid);
    %bar(39.8, 20.71, 15.0, 30.7, green, 0, solid);
    %bar(39.8, 31.42, 10.0, 41.42, red, 0, solid);
    %bar(39.8, 42.14, 32.0, 52.14, blue, 0, solid);
    %bar(39.8, 52.85, 33.0, 62.85, green, 0, solid);
    %bar(39.8, 63.57, 36.0, 73.57, red, 0, solid);
    %bar(39.8, 74.28, 35.0, 84.28, blue, 0, solid);
    %bar(39.8, 85.0, 33.0, 95.0, green, 0, solid);
 
       /* label female bars on left */
    %label(25.0, 20.0, '42', black, 0, 0, 4, swissb, 9);
    %label(15.0, 30.7, '77', black, 0, 0, 4, swissb, 9);
    %label(10.0, 41.42, '92', black, 0, 0, 4, swissb, 9);
    %label(32.0, 52.14, '26', black, 0, 0, 4, swissb, 9);
    %label(33.0, 62.85, '24', black, 0, 0, 4, swissb, 9);
    %label(36.0, 73.57, '13', black, 0, 0, 4, swissb, 9);
    %label(35.0, 84.28, '15', black, 0, 0, 4, swissb, 9);
    %label(33.0, 95.0, '19', black, 0, 0, 4, swissb, 9);
 run;
 
    /* define the titles */
 title1 'Population Tree';
 title2 h=4 'Distribution of Population by Sex';
 
    /* generate annotated slide */
 proc gslide annotate=poptree;
 run;
 quit;
tags: Problem Solvers, SAS 9.4, SAS Programmers

Comparing SAS/GRAPH® 9.4 capabilities with SAS/GRAPH® Version 6 was published on SAS Users.