Problem Solvers

5月 192017

Technical Support regularly receives incoming calls from customers who have encountered the following transcoding warning:

WARNING: Some character data was lost during transcoding in the data set Either the data contains characters that are not representable in the new encoding or truncation occurred during transcoding

People are not always exactly sure what this warning means nor what to do about it. No worries! This blog provides background information about why this warning occurs and offers resources that can help you understand and resolve any encoding or truncation issues that are the source of the warning above.

In most Western character sets, each character occupies only one byte of computer memory or storage. These are known as single-byte character sets (SBCS). Character sets for languages such as Japanese, Korean, and Simplified and Traditional Chinese require mixed-width encoding. The character sets for these languages are double-byte character sets (DBCS). (The term DBCS can be misleading because not all of the characters in a double-byte character set are two bytes. Thus, the term multibyte character set [MBCS] is sometimes used instead of DBCS.)

Unicode is a multibyte character set that was created to support all languages. It includes all characters from most modern written languages and historic scripts, even hieroglyphs and cuneiform. UTF-8 is one of the more common encoding forms of the Unicode standard, and this encoding is recommended as the SAS session encoding for multilingual environments if your data sources contain characters from many regions. Each character can be up to four bytes in UTF-8. UTF-8 is the SAS session encoding for SAS® Viya™. Clients such as SAS University Edition, SAS Studio, and SAS Visual Analytics typically execute statements in a server environment that run the UTF-8 encoding.

Now, let's look at how SAS handles data from different encodings along with two causes of the warning shown earlier.

Starting in SAS®9, SAS data sets store an encoding indicator in the descriptor portion. If the encoding value of the file differs from the encoding of the currently executing SAS session, the Cross Environment Data Access (CEDA) engine is invoked when SAS reads the data set. CEDA transcodes the data, by default. Transcoding is the process of converting the contents of SAS files from one encoding to another, and this process is necessary in order to read data from around the world.

When transcoding occurs, you receive an informational note about the use of CEDA. In addition, you might see the warning (shown above) in your SAS log.

The transcoding warning is issued when you have values for character variables that have either of the following characteristics:

  • The number of bytes for a character in one encoding do not match the number of bytes that are used for the same character in another encoding (as shown in the image below). If the length of the column is not wide enough to accommodate the additional bytes, truncation of the character data occurs.

  • The characters exist in one encoding, but they do not exist in another encoding. As a result, the transcoding is not successful.

If you use a procedure (for example, the PRINT or REPORT procedures), the procedure runs to completion. The variable is truncated if the additional bytes cause the value to exceed the length of the column. Alternatively, if a character from the data set does not also exist in the target encoding or if it occupies a different code point (after transcoding), the column might be blank or another character might be substituted for the original character. The procedure generates the transcoding warning.

If you have an output data set open, SAS transcodes the character data and stops writing observations to the file at the point it encounters the problematic characters. This behavior can result in either fewer observations than expected or a data set with zero observations. When this happens, SAS generates an error message instead of a warning. This error message contains the same content as the warning message.

ERROR: Some character data was lost during transcoding in the data set Either the data contains characters that are not representable in the new encoding or truncation occurred during transcoding

New Video Available to Help You Resolve the Transcoding Error or Warning

Before you attempt to resolve the transcoding error or warning, it is helpful to understand a bit about encodings and how to handle multilingual SAS data. To help you in this endeavor, SAS Technical Support worked with Education and Training to create a new, short (12 minutes) video to help you quickly troubleshoot and resolve the most common reasons for the transcoding error or warning.

Click the image below to access the video. (Note: You will be prompted to create a SAS profile if you do not have one already.)

Click image to play video


If you want to practice the techniques in this video, you can download the data sets that are used in the video here.

This video is based on solutions that have helped customers who called SAS Technical Support for assistance. The video offers these solutions in a visual format that is easy to follow. In addition to showing you how to launch SAS in different encodings to resolve the error, the video also introduces the Character Variable Padding (CVP) engine (Read-only) for processing SAS data files that contain multilingual data.

This video is one of a number of resources available to help you. The next section lists several tips that might help prevent or resolve transcoding warnings or errors.

Helpful Tips from the Field

The following tips have been tested and used in the field, and you might find them helpful when you work with multilingual data. The first two tips are usage tips; the others are programming tips.

  • Invoke SAS in more than one encoding. For this first tip, it is important to know that if you try to change the value of the ENCODING system option during a SAS session, SAS ignores the option. When this situation occurs, the following warning is generated:

WARNING 30-12: SAS option ENCODING is valid only at startup of the SAS System. The SAS option is ignored.

If you work with data that is stored in more than one encoding, you can invoke SAS in more than one session encoding and manage the sessions easily by setting the title bar to display the session encoding in the main SAS window.

Note: This solution only works in the Microsoft Windows operating environment. Display of UTF-8 encoded data in the SAS windowing environment is not fully supported.  SAS Enterprise Guide and SAS Studio have full support for display of UTF-8 characters.

To customize the title bar of the main SAS window.

1.  In a Windows environment, select Start All Programs SAS Additional Languages.

2.  Under Additional Languages, select the shortcut for the session encoding that you want. For example, the following image shows that SAS 9.4 (Unicode Support) is selected

3.  Right-click the shortcut and select Properties. This opens the properties dialog box for that shortcut.

4. On the Shortcut tab, place your cursor at the end of the current string in the Target text box. If it is not already listed in the box, enter the location of the configuration file that you want to use by using the –CONFIG option followed by the –AWSTITLE option.

Then include in quotation marks the text that you want to see in the main SAS window, as shown in the following example:

The entire line should look similar to the following, only it will appear as one long string:

"C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -CONFIG "C:\Program Files\SASHome\SASFoundation\9.4\nls\
u8\sasv9.cfg" -awstitle "SAS UTF8"

The following display shows the –AWSTITLE option added to the Target field of the SAS 9.4 (Unicode Support) Properties dialog box:

. Click OK.

Now, when you open SAS, you can immediately recognize the session encoding based on the value in the title bar.

You can pin this shortcut to your task bar by dragging it from the Start menu to the task bar. Then you should see your custom title when you hover your cursor over the shortcut.

  • Prevent an unexpected transcoding warning or error: If you read or convert a SAS data set from LATIN1 or WLATIN1 encoding to UTF-8 that contains Microsoft Word smart quotes or dashes in character variables, it can cause truncation errors. Those are not ASCII characters, and they require more than one byte when converted to UTF-8. For example, performing the following steps causes an unexpected transcoding error or warning.
    1. You use SAS in the WLATIN1 encoding.
    2. You copy a quoted string from Microsoft Word and paste that string into the DATALINES section of a SAS program in the SAS windowing environment.
    3. You create a new variable that uses the quoted string in its value.
    4. You execute the SAS program to create a permanent SAS data set.
    5. You invoke a UTF-8 SAS session to read the WLATIN1 data set in a procedure or DATA step.

You can prevent this issue by clearing the "Straight quotes" with "smart quotes" option in Word, as shown below. This option is available by selecting File Options Proofing AutoCorrect Option. On the AutoFormat tab, the option is listed under the Replace category.

  • Set the CVP engine explicitly (for UTF-8 sessions). The transcoding warning is common if you use a UTF-8 session encoding and the input data source is in the WLATIN1 encoding. The first 128 code points of the UTF-8 code page are identical to the 7-bit ASCII encoding. If your character data only contains characters within those 128 code points, SAS does not generate a warning or error when it transcodes the data. For code points above 128, the character variables must be expanded to two or more bytes. Many characters that are used in Western European languages fall into this category. You can address this issue and avoid the warning or error by adding the CVP engine to the LIBNAME statement for your input data source.

The following example LIBNAME statement illustrates how to specify the CVP engine explicitly:

libname mySAS cvp 'SAS-library-path';
data utf8dat;
set mySAS.wlatindat;

In this example, the code is submitted in a UTF-8 SAS session. The CVP engine is Read-only, and it is used to read the input data set from the WLATIN1 encoding.

  • Set the CVP engine implicitly. The CVP engine provides an easy way to convert your files and avoid truncation problems by using a default value of 1.5 times the original length of the character variable. However, that value might not be sufficient for the length of the characters in your data. The expansion length should be based on the characters that are contained in your data.

For example, if your data consists of double-byte character (DBCS) data (Chinese, Japanese, and so on), most characters are three bytes and emoji characters are four bytes. Therefore, the default 1.5 value for the CVP engine might not be enough for the number of additional bytes. A value of 2.5 is a reasonable expansion for DBCS characters, but you might need more.

In such cases, you might want to try setting the CVP engine implicitly. When you set the CVP engine implicitly, you can use either the CVPBYTES= or CVPMULTIPLIER= options. The following example uses the CVPBYTES= option to accommodate the four bytes that are required for the winking emoticon in UTF-8:

libname enc 'c:\public\encdata' cvpbytes=4;


  • Convert text in a character variable to another encoding. Sometimes, the CVP engine does not correct the transcoding error or warning. In these cases, Technical Support usually finds that the destination encoding does not support all of the characters found in the data. For example, if you have a UTF-8 data set that contains French, Greek, Hindi, and other characters and you are running a WLATIN1 session, SAS will successfully transcode the French and other Western European characters to WLATIN1. However, the Greek and Hindi characters cannot be transcoded because there is no representation for them in the WLATIN1 encoding. As a result, an error is generated, and the data is lost.

If you have this kind of multilingual data, you can use the KPROPDATA() function to transcode a character string from and to any encoding and convert characters that are not represented in the target encoding to a character of your choice (a question mark or a space). Note that you must specify BINARY or ANY as the input encoding to prevent CEDA from transcoding your data. (See the next tip for information about suppressing transcoding.) A macro is available in the technical paper Multilingual Computing with SAS 9.4 (on page 7) that you can run to accomplish these tasks.

  • Suppress transcoding. There are certain cases in which you might want to bypass transcoding errors. For example, suppose that you know that your data contains only ASCII characters. You are providing data sets to users and you do not know whether the users will run SAS in WLATIN1 or UTF-8 session encodings. Because the characters are ASCII, SAS does not need to transcode them. You do not want CEDA notes nor the extra overhead that CEDA requires. In such a case, you can suppress transcoding without risking data loss. In the LIBNAME statement for the output data set, specify either the OUTENCODING=ASCIIANY option or the ENCODING=ASCIIANY data set option.

Example 1:

libname final 'SAS-library-path' outencoding=asciiany; 
proc copy in=sashelp out=final noclone;
   select dsname;

Example 2:

libname final 'SAS-library-path'
data final.class(encoding=asciiany);
   set dsname;

The value of ASCIIANY means no transcoding occurs on ASCII machines. When the data source is open on an EBCDIC machine, SAS assumes that the data is ASCII and transcodes it into the EBCDIC session encoding. Note: The CONTENTS procedure shows the encoding value: us-ascii ASCII (ANSI).

The encoding options shown above have two other values:

  • ANY: With this value, SAS does not transcode at all, which is, effectively, binary mode.
  • EBCDICANY: With this value, transcoding only occurs on ASCII machines. SAS assumes the data is EBCDIC and transcodes the character data into the ASCII session encoding.

Additional Self-Help Resources

This section lists several documents that provide more detail about encoding concepts and suggests ways to handle transcoding problems.

  • Encoding: Helping SAS speak your language. This blog by Bari Lawhorn briefly explains what an encoding is, how to determine the default encoding, what to do if your encoding differs from that of other people with whom you share SAS data, and where to find more in-depth information about encodings.
  • Multilingual Computing with SAS® 9.4: This paper, which describes new and enhanced features for handling multiple languages in SAS 9.4, covers topics such as installing and configuring the SAS System, changing the locale of a deployment, working with multibyte data, multilingual support in SAS® Visual Analytics, and more
  • SAS® and UTF-8: Ultimately the Finest. Your Data and Applications Will Thank You!: This SAS Global Forum 2017 paper by Elizabeth Bales and Wei Zheng is a perfect go-to resource when you are migrating data files and formats from another encoding to UTF-8.
  • SAS® Encoding: Understanding the Details: This SAS Press book, by Manfred Kiefer, explains the basics about character encoding that are required for creating, manipulating, and rendering any type of character. This book also provide examples for troubleshooting a variety of encoding problems.
  • SAS® 9.4 National Language Support (NLS): Reference Guide, Fifth Edition. This user guide provides detailed information about encodings, transcoding, and other NLS topics (including dictionaries of NLS autocall macros, data set options, system options, formats, informats, functions, and procedures)
  • SAS Note 15597, "How to convert SAS data set encoding." This note explains how you can convert the encoding for a SAS data set.
  • SAS Note 52716, "The error "Some character data was lost during transcoding in the data set" occurs when the data set encoding does not match the SAS® session encoding."
  • SAS Sample 55054, "PROC SQL can be used to identify data representation and encoding for all data sets in a library:" This sample enables you to query a library of data sets that are in mixed encodings so you can plan to use them in compatible session encodings or you can convert the data sets to another encoding. Using the SQL procedure in this sample, you can create output similar to the following that shows you the data representation and encoding for all data sets in your library.



Demystifying and resolving common transcoding problems was published on SAS Users.

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=("")          from=("")
    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>';

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=("") from=("")
    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>';

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=("") from=("")
     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; 
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; 
  bubble x=horsepower y=mpg_city size=cylinders;
filename sendmail email to=("") from=("")
     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>';
      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 ( how close we are to making your files compliant with the final rule.


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)

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;
var type origin;
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!


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;
var type origin;
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 (

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


[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:


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
  • 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"
sheet_name="Sales Report"
proc print data=sashelp.orsales; 
title "Sales Report for the Year 1999";
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.


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;
class body / background=_undef_
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;
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.


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;
class body / background=_undef_ backgroundimage="C:background_large.jpg";
class header, rowheader, data / color=white 
ods excel file="c:temp.xlsx" options(embedded_titles="yes"
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;
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.


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;
class body / background=_undef_
ods excel file="c:temp.xlsx" style=styles.background_logo;
proc print data=sashelp.class;
ods excel close;


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;
proc report data=sashelp.class;
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.


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 "";

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.


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"
ods proclabel= "Detail Report of Males";
proc print data=sashelp.class(where=(sex="M"));
title link="#'The Table of Contents'!a1"  "Return to TOC";
ods proclabel= "Detail Report of Females";
proc print data=sashelp.class(where=(sex="F"));
title link="#'The Table of Contents!a1'"  "Return to TOC";
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.


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";
ods excel file="c:temp.xlsx" options(flow="tables");
proc print data=one label;
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.


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"


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:


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);


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;
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
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
                         display=(tickvalues line) reverse=true 
         barchart category=age response=Female / group=type orient=horizontal 
                                                 yaxis=y2 barlabel=true;
      layout overlay ❽/ walldisplay=none 
                         yaxisopts=(reverse=true display=none)
                         display=(tickvalues line)
         barchart category=age response=Male / group=type orient=horizontal 
                                               barlabel=true ;
proc sgrender data=ratio template=population;

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.


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

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

❶ 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:


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;
    /* create the Annotate data set, POPTREE */
 data poptree;
       /* length and type specification */
       /* 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);
    /* define the titles */
 title1 'Population Tree';
 title2 h=4 'Distribution of Population by Sex';
    /* generate annotated slide */
 proc gslide annotate=poptree;
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 '')
    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.”


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;
   %else %if &amp;_clientapp= 'SAS Enterprise Guide' %then %do;
    %put Running SAS Enterprise Guide; 
  %else %if %index(&amp;sysprocessname,DMS) %then %do;
    %put Running in Display Manager;
  %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;
  %mend 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:


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:


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.


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:

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

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

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.”


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.”


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; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="Audi";
title1 'Graph for BMW'; 
proc sgplot; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="BMW";
title1 'Graph for Volvo'; 
proc sgplot; 
  bubble x=horsepower y=mpg_city size=cylinders;
  where make="Volvo";
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;

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;

This code sample generates the following output:


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';

The following output is a partial display of the results:


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';

The following output shows the results:


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);
    define make;
data _null_;
  set and drivetrain="All"));
  file print ods=(template='mytable');
  put _ods_;

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 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);
   define make;

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;
     class drivetrain;
     var mpg_city;

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);

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;

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;

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;
           translate (Parameter = 'EV Location') into put(_val_, 10.5),
                   (Parameter = 'EV Scale') into put(_val_, 10.6);

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

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%';

proc template; 
     edit Base.Freq.OneWayList;
        edit frequency;  

        edit cumfrequency;
edit percent;
edit cumpercent;

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

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 " ";
        edit FVariable;                                           

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

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 $;

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 " ";

proc freq data=test;
   table c1;

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;

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

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

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;

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.