Problem Solvers

4月 212017
 

send an email that embeds a graphWhen using the SAS® system to email graphics output, a common request is to use SAS to send an email in which the graphics output is embedded in the body of the email. This functionality is not available until the second maintenance release for SAS® 9.4 (TS1M2). If you are using a version of SAS earlier than SAS 9.4 TS1M2, your best option is to create graphics output in a format such as RTF or PDF, and then attach the RTF or PDF file to your email.

Using the INLINED Option to Embed Graphics

If you are running SAS 9.4 TS1M2 or later, you can embed graphics output in an email. To do this, use the INLINED suboption with the ATTACH option in a SAS FILENAME statement that uses the EMAIL engine. Here is an example:

filename sendmail email to=("first.last@company.com")          from=("first.last@company.com")
    attach=("c:\temp\email.png" inlined='sgplot')
    type='text/html' subject="Emailing graphics output";

 

Then, later in your code, reference the value specified for the INLINED option in DATA step code that creates custom HTML output, as shown below:

                data _null_;  
        file sendmail;  
  put '<html>';
  put '<body>';
  put '<img src=cid:sgplot>';
  put '</body>';
  put '</html>';
run;

With this technique, although the graph is sent as an attachment, the attachment is hidden. When the email recipient opens the email, the attached graph is automatically displayed in the email (so that it looks like the graph is embedded in the body of the email).

Note: When using SAS to email graphics output, you must first set the EMAILSYS system option to SMTP and the EMAILHOST system option to the name of the SMTP email server at your site.

Embedding Multiple Graphs

You can also send multiple graphs in a single email using a SAS FILENAME statement as shown here:

            filename sendmail email to=("first.last@company.com") from=("first.last@company.com")
    attach=("c:\temp\email1.png" inlined='sgplot1'  "c:\temp\email2.png" inlined='sgplot2')
    type='text/html' subject="Emailing graphics output";

Then, create custom HTML output using DATA step code similar to the following:

     Data _null_;  
       file sendmail;  
 put '<html>';
 put '<body>';
 put '<img src=cid:sgplot1>';
 put '<img src=cid:sgplot2>';
 put '</body>';
 put '</html>';
    run;

Embedding a Graph and a PROC PRINT Table

This example shows how to embed a graph and PRINT procedure table in one email. Let us assume that you have a graph named sgplot.png stored in C:\Temp. You want to send an email using SAS that displays the SGPLOT graph in the body of the email directly before a table created with PROC PRINT. The following sample code demonstrates how to do this using a TITLE statement with PROC PRINT:

filename sendmail email  to=("first.last@company.com") from=("first.last@company.com")
     attach=("c:\temp\sgplot.png" inlined='sgplot') 
     type='text/html' subject="Email test of GRAPH output";
      ods _all_ close; 
ods html file=sendmail; 
title1 '<img src=cid:sgplot>';
proc print data=sashelp.class; 
run;
ods html close; 
ods listing; 
filename sendmail clear;

Embedding a Graph (Complete Program)

Here is a complete sample program that demonstrates embedding graphics in an email using graphics output created with the SGPLOT procedure:

%let workdir=%trim(%sysfunc(pathname(work)));
ods _ALL_ close; 
ods listing gpath="&workdir";
ods graphics / reset=index outputfmt=PNG imagename='email';  
title1 'Graph output emailed using SAS';
proc sgplot data=sashelp.cars; 
  bubble x=horsepower y=mpg_city size=cylinders;
run;
filename sendmail email to=("first.last@company.com") from=("first.last@company.com")
     attach=("&workdir./email.png" inlined='sgplot')
     type='text/html' subject="Emailing graphics output";
      data _null_;
 file sendmail;  
 put '<html>';
 put '<body>';
 put '<img src=cid:sgplot>';
 put '</body>';
 put '</html>';
run; 
      filename sendmail clear;

In conclusion, if you are running SAS 9.4 TS1M2 or later, using the INLINED option in a FILENAME statement is an excellent option when emailing graphics output.  Note that you can use this technique to email any graphics file in PNG, GIF, or JPEG format created with the SAS SG procedures, ODS Graphics, or SAS/GRAPH® procedures (such as GPLOT and GCHART).  You can also use this technique to email graphics files created with software other than SAS.

Use SAS to send an email that embeds a graph in the body of the email was published on SAS Users.

3月 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.

2月 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.

1月 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.

10月 212016
 

ProblemSolversHave you ever needed to run code based on the client application that you are using? Or have you needed to know the version of SAS® software that you are running and the operating system that you are running it on? This blog post describes a few automatic macro variables that can help with gathering this information.

Application Name

You can use the &_CLIENTAPP macro variable to obtain the name of the client application. Here are some details:

  • Referencing &_CLIENTAPP in SAS® Studio returns a value of SAS Studio
  • Referencing &_CLIENTAPP in SAS® Enterprise Guide® returns a value of ‘SAS Enterprise Guide
    Note: The quotation marks around SAS Enterprise Guide are part of the value.

Program Name

You can use the &SYSPROCESSNAME macro variable to obtain the name of the current SAS process. Here are some details:

  • Referencing &SYSPROCESSNAME interactively within the DMS window returns a value of DMS Process
  • Referencing &SYSPROCESSNAME in the SAS windowing environment of your second SAS session returns a value of DMS Process (2)
  • Referencing &SYSPROCESSNAME in SAS Enterprise Guide or SAS Studio returns a value of Object Server
  • Referencing &SYSPROCESSNAME in batch returns the word Program followed by the name of the program being run (for example: Program 'c:test.sas')
    Note: For information about other techniques for retrieving the program name, see SAS Note 24301: “How to retrieve the program name that is currently running in batch mode or interactively.”

Example

The following code illustrates how you can use both of these macro variables to check which client application you are using and display a message in the SAS log based on that result:

%macro check;
 
  %if %symexist(_clientapp) %then %do;
   %if &amp;_clientapp = SAS Studio %then %do;
    %put Running SAS Studio;
   %end;
   %else %if &amp;_clientapp= 'SAS Enterprise Guide' %then %do;
    %put Running SAS Enterprise Guide; 
   %end;
  %end;
 
  %else %if %index(&amp;sysprocessname,DMS) %then %do;
    %put Running in Display Manager;
  %end;
  %else %if %index(&amp;sysprocessname,Program) %then %do;
     %let prog=%qscan(%superq(sysprocessname),2,%str( ));
     %put Running in batch and the program running is &amp;prog;
  %end;
 
  %mend check;
 %check

SAS Session Run Mode or Server Type

Another helpful SAS read-only automatic macro variable is &SYSPROCESSMODE. You can use &SYSPROCESSMODE to obtain the current SAS session run mode or server type name. Here is a list of possible values:

• SAS Batch Mode

• SAS/CONNECT Session 

• SAS DMS Session

• SAS IntrNet Server

• SAS Line Mode

• SAS Metadata Server

• SAS OLAP Server

• SAS Pooled Workspace Server

• SAS Share Server

• SAS Stored Process Server

• SAS Table Server

• SAS Workspace Server

Operating System and Version of SAS

Having the information detailed above is helpful, but you might also need to know the operating system and exact version of SAS that you are running. The following macro variables help with obtaining this information.

You can use &SYSSCP and &SYSSCPL to obtain an abbreviation of the name of your operating system.  Here are some examples:

macrovariables

For a complete list of values, see the “SYSSCP and SYSSCPL Automatic Macro Variables” section of SAS® 9.4 Macro Language: Reference, Fourth Edition.

SAS Release

&SYSVLONG4 is the most informative of the macro variables that provide SAS release information. You can use it to obtain the release number and maintenance level of SAS as well as a four-digit year. Here is an example:

%put &amp;sysvlong4;

This code would print something similar to the following in the log:

9.04.01M3D06292015

Here is what this output means:

SAS release: 9.04.01

Maintenance level: M3

Ship Event date: D06292015

I hope that some of the tools described above are useful to you for obtaining information about your SAS environment. If you have any questions, please contact SAS Technical Support, and we will be happy to assist you. Thank you for using SAS!

tags: macro, Problem Solvers, SAS Macro, SAS Programmers

Macro variables that provide information about your SAS® environment was published on SAS Users.

9月 162016
 

ProblemSolversIf you use SAS® software to create a report that contains multiple graphs, you know that each graph appears on a separate page by default. But now you want to really impress your audience by putting multiple graphs on a page. Keep reading because this blog post describes how to achieve that goal.

With newer versions of SAS, there are many different options for putting multiple graphs on a single page. This blog post details these different options based on the following ODS destinations: ODS PDF, ODS RTF, and ODS HTML destinations.

To put multiple graphs on a page (whether you are using ODS or not), the SAS/GRAPH® procedure PROC GREPLAY is typically a good option and is mentioned several times in this blog post. For detailed information about using PROC GREPLAY, see SAS Note 48183: “Using PROC GREPLAY to put multiple graphs on the same page.”

The ODS PDF Destination

The ODS PDF destination is the most commonly used ODS destination for putting multiple graphs on a single page and it also offers the most options, which are described below.

STARTPAGE=NO Option

One way to put multiple graphs on a single PDF page is to use the STARTPAGE=NO option in the ODS PDF statement. Here is sample SAS code that demonstrates how to stack two SGPLOT graphs vertically on the same PDF page using the STARTPAGE=NO option in the ODS PDF statement:


title1; 
ods _all_ close; 
ods pdf file='c:tempsastest.pdf' startpage=no notoc dpi=300;

ods graphics / reset=all height=4in width=7in;  
proc sgplot data=sashelp.cars; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="Audi";
run;
proc sgplot data=sashelp.cars; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="BMW";
run;

ods pdf close;
ods preferences;  

Note that in the code above, the vertical height of the graphics output is reduced to 4 inches using the HEIGHT option in the ODS GRAPHICS statement. When using a traditional SAS/GRAPH procedure (such as GPLOT), you must specify the vertical height of your graph output using the VSIZE option in a GOPTIONS statement, as shown here:

goptions vsize=4in;

For sample code that demonstrates how to use the STARTPAGE=NO option in the ODS PDF statement to put four graphs on the same PDF page (two across and two down), see SAS Note 48569: “Use the STARTPAGE option in the ODS PDF statement to put multiple graphs on a single page in a PDF document.”

ODS LAYOUT

Another option for putting multiple graphs (and tables) on the same PDF page is ODS LAYOUT. With ODS PDF, absolute layout allows you to define specific regions on the page for your graph and table output. For sample code that demonstrates how to put multiple graphs and tables on the same PDF page using ODS LAYOUT, see SAS Note 55808: “ODS LAYOUT: Placing text, graphs, and images on the same PDF page.”

PROC GREPLAY

You can also put multiple graphs on the same page in a PDF document using the SAS/GRAPH procedure PROC GREPLAY. While the GREPLAY procedure has been around a long time, it is still a very useful option in many situations.

However, note that PROC GREPLAY can only replay graphics output that has previously been written to a SAS/GRAPH catalog, so it has the following limitations:

  • It cannot directly replay graphics output created with the SG procedures and ODS Graphics.
  • It cannot directly replay text-based output such as that created with Base® SAS procedures like PROC PRINT and PROC REPORT.

For sample SAS code that demonstrates how to put four GCHART graphs on the same PDF page using PROC GREPLAY, see SAS Note 44955: “Use PROC GREPLAY with the ODS PDF statement to place four graphs on the same page.”

For sample SAS code that demonstrates how to put six GCHART graphs on the same PDF page using PROC GREPLAY, see SAS Note 44973: “Use PROC GREPLAY to place size graphs on a single page in a PDF document.”

Although you can use PROC GREPLAY with graphics output created with the SG procedures and ODS Graphics, you must use the three-step process demonstrated in SAS Note 41461: “Put multiple PROC SGPLOT outputs on the same PDF page using PROC GREPLAY.”

The ODS RTF Destination

The following sample code demonstrates how to stack multiple graphs vertically on the same page using the SGPLOT procedure in combination with the STARTPAGE=NO option in the ODS RTF statement:

options nodate nonumber; 
 
ods _all_ close; 
ods rtf file='sastest.rtf' startpage=no image_dpi=300; 
 
ods graphics / reset=all outputfmt=png height=3in width=7in; 
 
title1 'Graph for Audi'; 
proc sgplot data=sashelp.cars; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="Audi";
run;
 
title1 'Graph for BMW'; 
proc sgplot data=sashelp.cars; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="BMW";
run;
 
title1 'Graph for Volvo'; 
proc sgplot data=sashelp.cars; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="Volvo";
run;
 
ods _all_ close; 
ods preferences;

Because ODS LAYOUT is not fully supported with the ODS RTF destination, I recommend using PROC GREPLAY if you want to arrange multiple graphs on the same RTF page in a grid. For sample SAS code that demonstrates how to put four graphs on a single page in an RTF document, see SAS Note 45147: “Use PROC GREPLAY to place four graphs on a single page in an RTF document.”

For sample SAS code that demonstrates how to put six graphs on a single page in a RTF document, see SAS Note 45150: “Use PROC GREPLAY to place six graphs on a single page in an RTF document.”

The ODS HTML Destination

When you create multiple graphs with the ODS HTML destination, they are stacked vertically on the same web page by default. You can scroll up and down through the graphics output using your web browser’s scroll bar. In most situations, using PROC GREPLAY is a good option for displaying multiple graphs on a single web page.

Another option is to use the HTMLPANEL ODS tagset to display a panel of graphs via the web. For documentation about using the HTMLPANEL tagset, see “The htmlpanel Tagset Controls Paneling.” For sample code that demonstrates how to use the HTMLPANEL ODS tagset to display multiple graphs and tables via the web in a two-by-two grid, see SAS Note 38066: “Use the HTMLPANEL ODS tagset to put multiple graphs and tables on the same web page.”

In conclusion, this blog post covers just a few of the methods you can use to put multiple graphs on a page. There are more options available than those discussed above. For example, for sample code that puts multiple graphs on the same page using PROC SGPANEL, see SAS Note 35049: “Risk panel graph.” For sample code that puts two graphs side-by-side using Graph Template Language and PROC SGRENDER, see SAS Note 49696: “Generate side-by-side graphs with Y and Y2 axes with the Graph Template Language (GTL).”

tags: ods, Problem Solvers, SAS ODS, SAS Programmers

Do you need multiple graphs on a page? We have got you covered! was published on SAS Users.

8月 202016
 

ProblemSolversIf you are using the second maintenance release of SAS 9.3 (TS1M2) or later, you might have noticed that you have several map-related libraries that are defined for you.

  • The MAPS library contains the old map data sets that have been provided with SAS/GRAPH® software for many years.  The source for these data sets was mainly freely available data or purchased data. As a result, it became difficult or impossible to provide updates to this data.
  • The MAPSGFK library contains new map data sets that are licensed through GfK Geomarketing and that are provided as part of SAS/GRAPH software.
  • The MAPSSAS library points to the same location as the MAPS library.

Determining which library to use

You should use the MAPSGFK data sets to produce your maps.  There are several advantages to using the MAPSGFK data sets:

  • The older MAPS library data sets contain outdated data, and this library will not be updated.
  • The MAPSGFK data is updated more frequently.
  • The MAPSGFK data sets standardize the variables in the data set. For example, the X and Y variables always contain the projected values, and LONG and LAT always contain the unprojected values.

Each of the data sets also contains the ID variable, as shown in the following example, to enable you to create a map without knowing about the boundaries that are contained in the data set.

proc gmap data=mapsgfk.ireland_attr map=mapsgfk.ireland;
id id;
choro idname;
run;
quit;

As mentioned above, the MAPSGFK library data sets contain both projected and unprojected values, which is helpful when you use annotation with maps or when you create a subset of a map.

  • The MAPSGFK.PROJPARM data set contains the parameters that were used when the projected data was created for each of the data sets.  You can use these parameters with the GPROJECT procedure when you project an annotate data set. For more information about projecting an annotate data set using MAPSGFK.PROJPARM, see the sample code that appears in the section Code to Project Annotate Data with a GfK Map Data Set ("Chapter 37: GMAP Procedure") in SAS/GRAPH® 9.4: Reference, Fourth Edition.
  • Many of the MAPSGFK data sets include a lower level of hierarchy  for boundaries than was available previously in the map data sets, as shown in this example:
proc gmap data=mapsgfk.africa1 map=mapsgfk.africa1;
   id id;
   choro id / nolegend;
run;
quit;

This code sample generates the following output:

MAPS, MAPSGFK and MAPSSAS

You can use the GREMOVE procedure to remove internal boundaries that you don’t want as part of your map.

  • In earlier releases, the names of the map data sets in the MAPS library were limited to eight characters. The MAPGSFK data sets do not have that restriction, so you can use longer names. The longer data-set names enable you to determine the map data-set content more easily.

Determining which MAPSGFK data sets to use

To help you determine which data set to use to create your map, you can use dictionary tables in the SQL procedure to generate a list of the data sets that are contained in the MAPSGFK library, along with their associated labels.

This example illustrates how you can view the dictionary table for selected data sets (in this case, MEMNAME and MEMLABEL):

proc sql;
   select memname, memlabel from dictionary.tables
          where libname='MAPSGFK';
quit;

The following output is a partial display of the results:

MAPS, MAPSGFK and MAPSSAS02

The data sets that end in _ATTR are the attribute data sets for the map data set of the same base name. You can use the attribute data sets to obtain names and other information that is associated with variables in the map data set.

You can determine more information about the contents of the data sets using the DICTIONARY.MEMBERS dictionary table in PROC SQL.

proc sql;
   select name, type, length, label from dictionary.columns
          where libname='MAPSGFK' and memname='NAMERICA';
quit;

The following output shows the results:

MAPS, MAPSGFK and MAPSSAS03

In some cases, changing your existing PROC GMAP code to use the MAPSGFK data sets rather than the MAPS data sets might be as simple as changing the data set name in the MAP= option of the PROC GMAP statement. In other cases, this changing data sets can include changing the variables that are listed in the ID statement of PROC GMAP, including using a GREMOVE procedure step to remove a lower-level map boundary to or remove or modify a GPROJECT procedure step. You can find tips about modifying your existing PROC GMAP code to work with MAPSGFK data sets in the section Using GfK Map Data Sets with Existing Code ("Chapter 37: GMAP Procedure") in  SAS/GRAPH® 9.4: Reference, Fourth Edition.

You can find more information about the new map data sets and modifying your existing map programs to use the MAPSGFK data sets in the SAS Global Forum paper The New SAS® Map Data Sets (by Darrell Massengill) and in the SAS/GRAPH Concepts section of SAS/GRAPH® 9.4: Reference, Fourth Edition.

tags: Problem Solvers, SAS Programmers

MAPS, MAPSGFK and MAPSSAS, Oh my! was published on SAS Users.

7月 162016
 

ProblemSolversWhen you work out, you probably have a routine set of exercises that you do. But if you read health-and-fitness websites or talk to a personal trainer, you know that for optimal fitness, you should vary your workout routine. Not only does adding variety to your fitness regime help you prevent injuries, it also helps build muscles by working different parts of your body. For example, if you’re a runner, you could add weights or another resistance workout once or twice a week. If you are a weight lifter, you might add yoga or Pilates to your repertoire to enhance your personal fitness. In a similar way, it can be beneficial for you as a SAS programmer to vary your coding methods by trying new things.

SAS programmers very often use the TEMPLATE procedure to create style templates that use the CLASS and STYLE statements. I am going to help you bulk up your PROC TEMPLATE muscles by showing you how to use the COMPUTE AS and TRANSLATE INTO statements to alter the structure of tables via table definitions.

The COMPUTE AS statement helps you create or modify columns of data. You can use this statement to create a column from raw data (similar to what you can do with the REPORT procedure), or you can use it with an existing table template. The following example uses a PROC TEMPLATE step that illustrates the former. This example uses an existing data set (the CARS data set that is shipped in the SASHELP library) and computes an average-miles-per-gallon variable called MPG_AVG:

proc template;
    define table mytable; 
       column make model mpg_city mpg_highway mpg_avg;
       define mpg_avg;
         header="Avg MPG";
         compute as mean(mpg_city,mpg_highway);
         format=3.;
         style={font_weight=bold};
    end;
    define make;
       blank_dups=on;
    end;
  end;
run;
 
data _null_;
  set sashelp.cars(where=(cylinders=4 and drivetrain="All"));
  file print ods=(template='mytable');
  put _ods_;
run;

Here are partial results from the example code:

Using the COMPUTE AS and TRANSLATE INTO Statements

You’ll notice that I just could not resist sticking with part of my normal PROC TEMPLATE routine when I added a style change to the new column MPG_AVG. I also made the values in that column stand out by using bold font. In addition, because I like to prevent duplicates from appearing, I used the BLANK_DUPS=ON column attribute for the MAKE column.

If you want to try a cool new trick that is available in SAS® 9.4, try the following ODSTABLE procedure. This code does exactly what the previous example does, only with fewer keystrokes. It's similar to combining a strength and cardio move in your exercise routine!

proc odstable
   data=sashelp.cars(where=(cylinders=4 and drivetrain="All"));
   column make model mpg_city mpg_highway mpg_avg;
   define mpg_avg;
      header="Avg MPG";
      compute as mean(mpg_city,mpg_highway);
         format=3.;
         style={font_weight=bold};
   end;
   define make;
      blank_dups=on;
   end;
run;

Most procedures (except for PRINT, REPORT, and TABULATE) use an existing table template. You can determine the table template that a procedure uses by reviewing the log information that is generated when the ODS TRACE ON statement is in effect. For example, based on the log information from an ODS TRACE ON statement, we know that the following MEANS procedure code uses a table template called BASE.SUMMARY.

proc means data=sashelp.cars;
     class drivetrain;
     var mpg_city;
run;

If you submit this PROC MEANS code, you obtain the results shown below:

Using the COMPUTE AS and TRANSLATE INTO Statements02

In the following example, I use the COMPUTE AS statement to edit the MEAN column by multiplying the mean statistic by 10:

proc template;
   edit base.summary;
   edit mean 
      compute as (mean * 10);
         format=6.1;
      end;
   end;
run;

Here are the results that are generated when you run the PROC MEANS step a second time (with an explanatory footnote added):

Using the COMPUTE AS and TRANSLATE INTO Statements06

It is important to note that the default location where PROC TEMPLATE stores results is the SASUSER library. Therefore, subsequent PROC MEANS steps also multiply the mean by 10 unless you delete this table template. Just like a personal trainer recommends stretching after each workout in order to avoid sore muscles, I recommend the following PROC TEMPLATE step to delete the updated table template after the PROC MEANS step:

proc template;
   delete base.summary;
run;

The COMPUTE AS statement is useful when you need to create or edit an entire column. However, in many of the statistical procedures, the reported statistics are stacked. Technical Support has received requests from SAS programmers to make changes to just one of the statistics in a column. Therefore, when you want to change a single cell in a table, I recommend that you exercise the TRANSLATE INTO statement.

Consider the following code in which the Parameter Estimates object is requested from the RELIABILITY procedure:

ods select ParmEst;
 
proc reliability data=fan;
   distribution Weibull;
   pplot lifetime*censor( 1 ) / covb;
run;

Note: The DATA=FAN option in the code above links to the online documentation where you can find the DATA step that builds the data set FAN.

The code above displays the results shown in this table:

Using the COMPUTE AS and TRANSLATE INTO Statements04

In this table, the Estimate column displays the parameter estimates. If you need to modify just one set of parameters (for example, just the Extreme Value [EV] parameter estimates), you can use the TRANSLATE INTO statement to do the job.

The TRANSLATE INTO statement applies conditional logic to effectively change the formatting of the Estimate column only for the EV Location and EV Scale parameters. In the following example, the Estimate column for the EV Location parameter is displayed with the 10.5 format. In addition, the EV Scale parameter is displayed with a 10.6 format, while the remaining cells are displayed with the default format applied, which is specified as 10.4.

proc template;
     edit qc.reliability.ppest;
        edit estimate;
           header="Estimate";
		format=10.4;
           translate (Parameter = 'EV Location') into put(_val_, 10.5),
                   (Parameter = 'EV Scale') into put(_val_, 10.6);
      end;
     end;
run;

When you rerun the PROC RELIABILITY step that is shown above, you get these results:

Using the COMPUTE AS and TRANSLATE INTO Statements05

The changes that you make to your statistics can be much more dramatic. Just like with your workout, the more effort you put into it, the more defined your results!

The samples and SAS Notes found on support.sas.com focus on the DEFINE, STYLE, and CLASS statements in PROC TEMPLATE. These statements are fabulous tools in the PROC TEMPLATE routine that enhance your ODS results because they help change the style of your Excel, HTML, RTF, and PDF results.  By adding the COMPUTE AS and TRANSLATE INTO statements to your tools, you can round out your ODS routine to further enhance your ODS results. But if you find that you need the help of a personal trainer (that is, a Technical Support consultant), call Technical Support or send email to support@sas.com!

Are you ready to enhance your PROC TEMPLATE programming routine even more? If so, here are some additional resources:

tags: Problem Solvers, SAS Programmers

Build your PROC TEMPLATE muscles: Using the COMPUTE AS and TRANSLATE INTO statements was published on SAS Users.

5月 202016
 

ProblemSolversPROC FREQ is often the first choice when you want to generate basic frequency counts, but it is the last choice when it is compared to other statistical reporting procedures. People sometimes consider PROC FREQ last because they think they have little or no control over the appearance of the output. For example, PROC FREQ does not allow style options within the syntax, which the REPORT and TABULATE procedures do allow. Also, you cannot control the formats or headings with statements in the procedure step.

Sometimes, a simple frequency (via a one-way table) is all you want, and you don’t want to have to create an output data set just to add a format. A one-way table in PROC FREQ is unique also in that it includes a cumulative count and percent. These calculations cannot be done in other procedures without additional code or steps. However, there is a simple way to make some basic modifications to your table. By adding a PROC TEMPLATE step to modify either the Base.Freq.OneWayFreqs or the Base.Freq.OneWayList table template, you can change the formats of the statistics, move the label of the variable, change the labels of the statistics, and suppress the Frequency Missing row that appears below the table. These changes apply to all output destinations, including the traditional listing output.  You can also use PROC TEMPLATE to make small modifications to the nodes that are generated by one-way tables in the table of contents for non-listing ODS destinations.

Customize the Formats and Labels for Statistics

If you want to modify the format for the statistics Frequency, Cumulative Frequency, Percent, and Cumulative Percent, you can use the FORMAT= option in PROC TEMPLATE. This option accepts SAS formats and user-defined formats. If you want to change the statistic heading, you can use the HEADER= option.

The following example uses both the FORMAT= and the HEADER= statements to format the statistics values and to change the Frequency heading to Count. This example also changes the Percent heading to Pct.

proc format;
     picture pctfmt (round) other='009.9%';
run;

proc template; 
     edit Base.Freq.OneWayList;
        edit frequency;  
           header='Count'; 
           format=comma12.;
        end;  

        edit cumfrequency;
           format=comma12.; 
        end;
edit percent;
header='Pct';
format=pctfmt.;
end;
   
edit cumpercent;
format=pctfmt.; 
end; 
end;
run;                                                                                  

data class;
     set sashelp.class;
     wgt=1000;
run;
 
proc freq data=class;
     tables age;
     weight wgt; 
run;

This code generates the following table:

FREQ procedure's one-way tables01

Move the Variable Label

If a variable has a label, it is centered, by default, at the top of the table, and the variable name appears above the column of values.

FREQ procedure's one-way tables02

If you want to use the label instead of the name above the column, you can edit the HEADER= value in PROC TEMPLATE, as shown in the following example:

proc template;
   edit Base.Freq.OneWayList;             
        edit h1; 
                /* Set text to a blank instead of VARLABEL. */ 
             text " ";
        end;                                                
                                                                                                  
        edit FVariable;                                           
           just=varjust;
           style=rowheader;
           id;
           generic;
           header=varlabel;
        end;
     end;
  run;

proc freq data=sashelp.class;
   label age='Age in Years';
   tables age;
run;

This code generates the following table, which replaces the default location of the label with blank text and moves the label so that it is above the column of the variable's values, as shown in this example:

FREQ procedure's one-way tables03

Suppress the Frequency Missing= Row

If a variable has missing values and you do not include the MISSING option in the TABLES statement of PROC FREQ, the output includes the frequency of missing values (Frequency Missing=) below the table with the number of missing values.

The following table shows the default output:

FREQ procedure's one-way tables04

To suppress this line without including missing values in the table output, you can modify the table template, as follows:

data test;
input c1 $;
cards;
a
b
.
;
run;

proc template;
     edit Base.Freq.OneWayFreqs; 

   /* This is the default setting:                     */ 
   /*  define f2;                                      */
   /*     text "Frequency Missing =" fMissing -12.99; */
   /*     print = pfoot2;                              */
   /*  end;                                            */
        edit f2; 
           text " ";
        end;
     end;
run;

proc freq data=test;
   table c1;
run;

The table that is generated by this code does not contain the Frequency Missing= row at the bottom of the table.

FREQ procedure's one-way tables05

Customize the Table of Contents

For a one-way table, the default table of contents looks like this:

FREQ procedure's one-way tables06

To control the first node, The Freq Procedure, you can use the ODS PROCLABEL statement.  You can change the text of this node, but you cannot eliminate the node. You can remove the One-Way Frequencies node using PROC TEMPLATE with the CONTENTS= statement. The following example changes the text of the The Freq Procedure node, and it eliminates the One-Way Frequencies node.

proc template;
   edit Base.Freq.OneWayFreqs;
      contents=off;
   end;
run;

ods listing close;
ods pdf file='test.pdf';

ods proclabel='My One-Way Table';
proc freq data=sashelp.class;
tables age;
run;

ods pdf close;
ods listing;

Here is the modified table of contents:

FREQ procedure's one-way tables07

(For additional customization of the table of contents, you need to consider using the DOCUMENT procedure.  The SAS Global Forum 2011 paper Let's Give 'Em Something to TOC about: Transforming the Table of Contents of Your PDF File, by Bari Lawhorn, contains useful information and examples that illustrate how to use PROC DOCUMENT, PROC TEMPLATE, and other SAS statements and options to customize your TOC.)

To restore the default table template, run the following code:

proc template;
delete Base.Freq.OneWayFreqs;
delete Base.Freq.OneWayList;
run;

The examples above show how you actually can customize PROC FREQ one-way tables, with a little help from PROC TEMPLATE. PROC TEMPLATE offers several methods that make it easy to customize one-way, frequency-count tables that enhance table format and appearance.
tags: Problem Solvers, PROC FREQ, SAS Programmers

Seeing the FREQ procedure's one-way tables in a new light was published on SAS Users.

4月 222016
 

ProblemSolversThe DS2 programming language gives you the following powerful capabilities:

  • The precision that results from using the new supported data types
  • Access to the new expressions, write methods, and packages available in the DS2 syntax
  • Ability to execute SAS Federated Query Language (FedSQL) from within the DS2 program
  • Ability to execute code outside of a SAS session such as on SAS® High-Performance Analytics Server or the SAS® Federation Server
  • Access to the threaded processing in products such as the SAS® In-Database Code Accelerator, SAS High-Performance Analytics Server, and SAS® Enterprise Miner™

Some DATA step functionality is not available in DS2, at least not how you are used to. However, don’t lose hope, because this article discusses ways to mimic some of the missing DATA step features within DS2.

Simulate Missing SET Statement Data Set Options

Many of the SET statement options are not allowed within DS2, such as the OBS= data set option. However, you can simulate some of these options by using FedSQL. For example, you can use a LIMIT clause similar to the following in the place of OBS=:

{select * from work.temp limit 10}.

Here is an example:

data one;                                                                                                                               
   do i = 1 to 100;                                                                                                                       
      output;                                                                                                                               
   end;                                                                                                                                   
run;                                                                                                                                    
                                                                                                                                        
proc ds2;                                                                                                                               
data new(overwrite=yes);                                                                                                               
   method run();                                                                                                                         
      set {select * from work.one limit 10};                                                                                               
   end;                                                                                                                                  
enddata;                                                                                                                               
run;                                                                                                                                    
quit;                                                                                                                                   
                                                                                                                                        
proc print data=new;                                                                                                                    
run;    

 

Prevent Errors from Duplicate Data Set Names

In a DATA step, an automatic overwrite occurs when you issue a DATA statement that contains a data set name that was used previously in your SAS session.

For example, in the DATA step, you can add the following code:

data one;
   x=100;
run;
data one;
   x=200;
run;

This code overwrites the previous ONE data set. However, this automatic overwrite does not occur within DS2, and an error is generated if a specified data set name already exists. To work around this problem, you can use the OVERWRITE option as shown below.
proc ds2;
data one(overwrite=yes);
   dcl double x;
   method init();
      x=100;
   end;
run;
quit;

Specify Name Literals

In a DATA step, you can use name literals. However, in DS2, they are specified differently.

In Base SAS® with the VALIDVARNAME system option set to ANY, you can use a name literal like the following:

'My var'n=100;

This strategy does not work in DS2, but you can use double quotation marks to get the same results:

"My var"=100;

Substitute Missing Statements

The ATTRIB, LABEL, LENGTH, FORMAT, and INFORMAT statements are missing from DS2. However, you can use the DECLARE statement with the HAVING clause to perform these functions.

Here is an example:

dcl double aa having
   label 'var aa'
   format comma8.2;

Create an Array

In Base SAS, you use the ARRAY statement in a DATA step to create an array to reference variables within the program data vector (PDV). Here is an example:

data one;                                                                                                                               
   array list(4) x1-x4;                                                                                                                  
   do i = 1 to 4;                                                                                                               
      list(i)=i;                                                                                                                          
   end;                                                                                                                                 
run;

The ARRAY statement does not exist in DS2, but the following code shows how to use an equivalent statement called VARARRAY:
proc ds2;                                                                                                                               
data one(overwrite=yes);                                                                                                               
   vararray double x[4];                                                                                                                 
   declare double i;                                                                                                                     
   method init();                                                                                                                       
   do i = 1 to 4;                                                             
      x[i]=i;                                                                                                                            
   end;                                                                                                                                
   end;                                                                                                                                 
enddata;                                                                                                                               
run;                                                                                                                                    
quit;  

Note: The VARARRAY statement must be outside the METHOD statement.

Enable Macro Variable Resolution

To reference a macro variable as a character value in the DATA step, you place double quotation marks around the macro variable as shown in the following example:

%let val=This is a test;
data _null_;
   dval=”&amp;val”;
   put dval=;
run;

In DS2, double quotation marks are used only to delimit an identifier. Single quotation marks are required to delimit constants. If the above code was run within DS2, a warning similar to the following would occur:

Solutions for missing DATA step features within DS2

To get a better understanding of the difference between an identifier and constant text, consider the following two examples:

VARA=’test’;
VARB=”vara”;

Within DS2, the first assignment statement creates a variable called VARA and assigns it a text string of test. The second assignment statement creates a variable called VARB and also assigns it a text string of test. Since the second assignment statement is using double quotation marks, vara is seen as an identifier and the identifier’s value is placed into the variable VARB.

Since constant text is represented by single quotation marks in DS2, there needs to be a way to resolve the macro variable within quotation marks. Luckily, within DS2, there is a SAS supplied autocall macro called %TSLIT that enables macro variable resolution within single quotation marks.  Here is an example:

%let val=This is a test;                                                                                                                
proc ds2;                                                                                                                               
data _null_;                                                                                                                           
   method init();                                                                                                                         
      declare char(14) dval;                                                                                                               
      dval=%tslit(&amp;val);                                                                                                                  
      put dval=;                                                                                                                          
   end;                                                                                                                                   
enddata;                                                                                                                               
run;                                                                                                                                    
quit;

I hope this blog post has been helpful. If you have any questions, please contact SAS Technical Support and we will be happy to assist you. Thanks for using SAS!

 

tags: Problem Solvers, PROC DS2, SAS Programmers

Solutions for missing DATA step features within DS2 was published on SAS Users.