Chevell Parker

5月 152020
 
This blog demonstrates how to modify your ODS HTML code to make your column headers “sticky,” or fixed in position. Using sticky headers is most beneficial when you have long tables on your web page and you want the column headers to stay in view while scrolling through the rest of the page. The ability to add sticky headers was added with CSS 2.1, with the cascading style sheet (CSS) position property and its sticky value. You might have seen this capability before CSS 2.1 because it was supported by WebKit, which is a browser engine that Apple developed and is used primarily in the Safari browser (In Safari, you use the position property with the value -webkit-sticky.) The position: sticky style property is supported in the latest versions of the major browsers, except for Internet Explorer. The FROZEN_HEADERS= option can be used with the TableEditor tagset; see the TableEditor tagset method below.

Before you start

Here is a brief explanation about the task that this blog helps you accomplish. Since the position: sticky style property is supported with the <TH> HTML tags within tables, it is very easy for you to add the position: sticky style for HTML tables that ODS HTML generates. When this CSS style attribute is added for the headers, the headers are fixed within the viewport, which is the viewable area. The content in the viewport is scrollable, as seen in the example output below.

In the past, JavaScript was the main tool for generating fixed headers that are compatible across browsers and devices. However, the position: sticky property has also made it easier to fix various other elements, such as footers, within the viewport on the web page. This blog demonstrates how to make the <TH> tag or .header class sticky but enable the rest of the web page to be scrolled. The techniques here work for both desktop and mobile applications. There are multiple ways to add this style. Choose the method that is most convenient for you.

Method 1: Use the HEADTEXT= option

This example uses the position: sticky style property for the .header class, which is added to the HEADTEXT= option in the ODS HTML statement. The .header class is added along with the position style property between the <HEAD> and </HEAD> tags, which is the header section of the web page. This method is very convenient. However, you are limited to 256 characters and you might want to add other CSS style properties. The position style property is added using the .header class name, which is used by ODS HTML to add style attributes to the column headers. As the name suggests, cascading elements cascade and enable elements with like names to be combined. In the following code example, the HEADTEXT= option uses a CSS rule with the .header class and the position: sticky property for the header section of the web page.

ods html path="c:\temp" file="sticky.html"
headtext="<style> .header {position: sticky;top:0}</style>";
 
proc print data=sashelp.cars;
run;
 
ods html close;

Here is what the output looks like:

Method 2: Use the STYLESHEET= option

You can also add the position: sticky property to the .header class from an external CSS file, which can be referenced in ODS HTML code by using the STYLESHEET= option with the (URL=) suboption. This method uses a CSS file as a basis for the formatting, unlike the first method above, which had applied the default HTMLBLUE style for the destination.

Another item worth mentioning in this second example is the grouping of the CSS class selectors, which match the style element names used with ODS and the TEMPLATE procedure. For example, the .body, .systemtitle, .header, .rowheader, and .data class selectors are added and grouped into the font-family style property. This method is also used for several of the other style properties below. The .data class adds some additional functionality worth discussing, such as the use of a pseudo style selector, which applies a different background color for even alternating rows. In the example below, the .class names and the template element names are the same. You should place the CSS style rules that are shown here in a file that is named sticky.css.

.body, .systemtitle, .header, .rowheader, .data { 
font-family: arial, sans-serif; 
}  
.systemtitle, .header, .rowheader { 
font-weight: bold
} 
.table, .header, .rowheader, .data { 
border-spacing: 0; 
border-collapse: collapse; 
border: 1px solid #606060;
} 
.table tbody tr:nth-child(even) td { 
background-color: #e0e0e0; 
color: black;
}
.header { 
background-color: #e0e0e0;
position: -webkit-sticky;
position: sticky;
top:0;
} 
.header, .rowheader, .data { 
padding: 5px 10px;
}

After you create that CSS file, you can use the ODS HTML statement with the STYLESHEET= option. In that option, the (URL=) suboption uses the sticky.css file as the basis for the formatting. Forgetting to add the (URL=) suboption re-creates a CSS file with the current template style that is being used.

ods html path="c:\temp" file="sticky.html"
   stylesheet=(url="sticky.css");
proc print data=sashelp.cars;
run; 
ods html close;

Here is what the output looks like:

The pseudo class selector in the CSS file indicated that even alternating rows for all <TD> tags would be colored with the background color gray. Also, the position: sticky property in the .header class fixed the position of the header within the viewport.

Method 3: Use the TableEditor tagset

A third method uses the TableEditor tagset, which enables sticky headers to be added by using options. Options are also applied to modify the style for the alternating even and odd rows as well as to have sortable headers.

/* Reference the TableEditor tagset from support.sas.com. */
filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
/* Insert the tagset into the search path for ODS templates. */
ods path(Prepend) work.templat(update);
%include tpl;
ods tagsets.tableeditor file="c:\output\temp.html" 
options(sticky_headers="yes"
sort="yes"
banner_color_even="#e0e0e0") style=htmlblue;
 
proc print data=sashelp.cars;
run;
 
ods tagsets.tableeditor close;

Here is what the output looks like:

In summary, this article describes how easy it is to add sticky headers to tables that are generated by using the ODS HTML destination. Adding fixed headers to any table allows the output to dynamically preserve the headers in the viewable area while scrolling through the table, allowing a much richer experience. Give it a try and let me know how it goes.

Learn More

How to Add Sticky Headers with ODS HTML was published on SAS Users.

5月 152020
 
This blog demonstrates how to modify your ODS HTML code to make your column headers “sticky,” or fixed in position. Using sticky headers is most beneficial when you have long tables on your web page and you want the column headers to stay in view while scrolling through the rest of the page. The ability to add sticky headers was added with CSS 2.1, with the cascading style sheet (CSS) position property and its sticky value. You might have seen this capability before CSS 2.1 because it was supported by WebKit, which is a browser engine that Apple developed and is used primarily in the Safari browser (In Safari, you use the position property with the value -webkit-sticky.) The position: sticky style property is supported in the latest versions of the major browsers, except for Internet Explorer. The FROZEN_HEADERS= option can be used with the TableEditor tagset; see the TableEditor tagset method below.

Before you start

Here is a brief explanation about the task that this blog helps you accomplish. Since the position: sticky style property is supported with the <TH> HTML tags within tables, it is very easy for you to add the position: sticky style for HTML tables that ODS HTML generates. When this CSS style attribute is added for the headers, the headers are fixed within the viewport, which is the viewable area. The content in the viewport is scrollable, as seen in the example output below.

In the past, JavaScript was the main tool for generating fixed headers that are compatible across browsers and devices. However, the position: sticky property has also made it easier to fix various other elements, such as footers, within the viewport on the web page. This blog demonstrates how to make the <TH> tag or .header class sticky but enable the rest of the web page to be scrolled. The techniques here work for both desktop and mobile applications. There are multiple ways to add this style. Choose the method that is most convenient for you.

Method 1: Use the HEADTEXT= option

This example uses the position: sticky style property for the .header class, which is added to the HEADTEXT= option in the ODS HTML statement. The .header class is added along with the position style property between the <HEAD> and </HEAD> tags, which is the header section of the web page. This method is very convenient. However, you are limited to 256 characters and you might want to add other CSS style properties. The position style property is added using the .header class name, which is used by ODS HTML to add style attributes to the column headers. As the name suggests, cascading elements cascade and enable elements with like names to be combined. In the following code example, the HEADTEXT= option uses a CSS rule with the .header class and the position: sticky property for the header section of the web page.

ods html path="c:\temp" file="sticky.html"
headtext="<style> .header {position: sticky;top:0}</style>";
 
proc print data=sashelp.cars;
run;
 
ods html close;

Here is what the output looks like:

Method 2: Use the STYLESHEET= option

You can also add the position: sticky property to the .header class from an external CSS file, which can be referenced in ODS HTML code by using the STYLESHEET= option with the (URL=) suboption. This method uses a CSS file as a basis for the formatting, unlike the first method above, which had applied the default HTMLBLUE style for the destination.

Another item worth mentioning in this second example is the grouping of the CSS class selectors, which match the style element names used with ODS and the TEMPLATE procedure. For example, the .body, .systemtitle, .header, .rowheader, and .data class selectors are added and grouped into the font-family style property. This method is also used for several of the other style properties below. The .data class adds some additional functionality worth discussing, such as the use of a pseudo style selector, which applies a different background color for even alternating rows. In the example below, the .class names and the template element names are the same. You should place the CSS style rules that are shown here in a file that is named sticky.css.

.body, .systemtitle, .header, .rowheader, .data { 
font-family: arial, sans-serif; 
}  
.systemtitle, .header, .rowheader { 
font-weight: bold
} 
.table, .header, .rowheader, .data { 
border-spacing: 0; 
border-collapse: collapse; 
border: 1px solid #606060;
} 
.table tbody tr:nth-child(even) td { 
background-color: #e0e0e0; 
color: black;
}
.header { 
background-color: #e0e0e0;
position: -webkit-sticky;
position: sticky;
top:0;
} 
.header, .rowheader, .data { 
padding: 5px 10px;
}

After you create that CSS file, you can use the ODS HTML statement with the STYLESHEET= option. In that option, the (URL=) suboption uses the sticky.css file as the basis for the formatting. Forgetting to add the (URL=) suboption re-creates a CSS file with the current template style that is being used.

ods html path="c:\temp" file="sticky.html"
   stylesheet=(url="sticky.css");
proc print data=sashelp.cars;
run; 
ods html close;

Here is what the output looks like:

The pseudo class selector in the CSS file indicated that even alternating rows for all <TD> tags would be colored with the background color gray. Also, the position: sticky property in the .header class fixed the position of the header within the viewport.

Method 3: Use the TableEditor tagset

A third method uses the TableEditor tagset, which enables sticky headers to be added by using options. Options are also applied to modify the style for the alternating even and odd rows as well as to have sortable headers.

/* Reference the TableEditor tagset from support.sas.com. */
filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
/* Insert the tagset into the search path for ODS templates. */
ods path(Prepend) work.templat(update);
%include tpl;
ods tagsets.tableeditor file="c:\output\temp.html" 
options(sticky_headers="yes"
sort="yes"
banner_color_even="#e0e0e0") style=htmlblue;
 
proc print data=sashelp.cars;
run;
 
ods tagsets.tableeditor close;

Here is what the output looks like:

In summary, this article describes how easy it is to add sticky headers to tables that are generated by using the ODS HTML destination. Adding fixed headers to any table allows the output to dynamically preserve the headers in the viewable area while scrolling through the table, allowing a much richer experience. Give it a try and let me know how it goes.

Learn More

How to Add Sticky Headers with ODS HTML was published on SAS Users.

5月 152020
 
This blog demonstrates how to modify your ODS HTML code to make your column headers “sticky,” or fixed in position. Using sticky headers is most beneficial when you have long tables on your web page and you want the column headers to stay in view while scrolling through the rest of the page. The ability to add sticky headers was added with CSS 2.1, with the cascading style sheet (CSS) position property and its sticky value. You might have seen this capability before CSS 2.1 because it was supported by WebKit, which is a browser engine that Apple developed and is used primarily in the Safari browser (In Safari, you use the position property with the value -webkit-sticky.) The position: sticky style property is supported in the latest versions of the major browsers, except for Internet Explorer. The FROZEN_HEADERS= option can be used with the TableEditor tagset; see the TableEditor tagset method below.

Before you start

Here is a brief explanation about the task that this blog helps you accomplish. Since the position: sticky style property is supported with the <TH> HTML tags within tables, it is very easy for you to add the position: sticky style for HTML tables that ODS HTML generates. When this CSS style attribute is added for the headers, the headers are fixed within the viewport, which is the viewable area. The content in the viewport is scrollable, as seen in the example output below.

In the past, JavaScript was the main tool for generating fixed headers that are compatible across browsers and devices. However, the position: sticky property has also made it easier to fix various other elements, such as footers, within the viewport on the web page. This blog demonstrates how to make the <TH> tag or .header class sticky but enable the rest of the web page to be scrolled. The techniques here work for both desktop and mobile applications. There are multiple ways to add this style. Choose the method that is most convenient for you.

Method 1: Use the HEADTEXT= option

This example uses the position: sticky style property for the .header class, which is added to the HEADTEXT= option in the ODS HTML statement. The .header class is added along with the position style property between the <HEAD> and </HEAD> tags, which is the header section of the web page. This method is very convenient. However, you are limited to 256 characters and you might want to add other CSS style properties. The position style property is added using the .header class name, which is used by ODS HTML to add style attributes to the column headers. As the name suggests, cascading elements cascade and enable elements with like names to be combined. In the following code example, the HEADTEXT= option uses a CSS rule with the .header class and the position: sticky property for the header section of the web page.

ods html path="c:\temp" file="sticky.html"
headtext="<style> .header {position: sticky;top:0}</style>";
 
proc print data=sashelp.cars;
run;
 
ods html close;

Here is what the output looks like:

Method 2: Use the STYLESHEET= option

You can also add the position: sticky property to the .header class from an external CSS file, which can be referenced in ODS HTML code by using the STYLESHEET= option with the (URL=) suboption. This method uses a CSS file as a basis for the formatting, unlike the first method above, which had applied the default HTMLBLUE style for the destination.

Another item worth mentioning in this second example is the grouping of the CSS class selectors, which match the style element names used with ODS and the TEMPLATE procedure. For example, the .body, .systemtitle, .header, .rowheader, and .data class selectors are added and grouped into the font-family style property. This method is also used for several of the other style properties below. The .data class adds some additional functionality worth discussing, such as the use of a pseudo style selector, which applies a different background color for even alternating rows. In the example below, the .class names and the template element names are the same. You should place the CSS style rules that are shown here in a file that is named sticky.css.

.body, .systemtitle, .header, .rowheader, .data { 
font-family: arial, sans-serif; 
}  
.systemtitle, .header, .rowheader { 
font-weight: bold
} 
.table, .header, .rowheader, .data { 
border-spacing: 0; 
border-collapse: collapse; 
border: 1px solid #606060;
} 
.table tbody tr:nth-child(even) td { 
background-color: #e0e0e0; 
color: black;
}
.header { 
background-color: #e0e0e0;
position: -webkit-sticky;
position: sticky;
top:0;
} 
.header, .rowheader, .data { 
padding: 5px 10px;
}

After you create that CSS file, you can use the ODS HTML statement with the STYLESHEET= option. In that option, the (URL=) suboption uses the sticky.css file as the basis for the formatting. Forgetting to add the (URL=) suboption re-creates a CSS file with the current template style that is being used.

ods html path="c:\temp" file="sticky.html"
   stylesheet=(url="sticky.css");
proc print data=sashelp.cars;
run; 
ods html close;

Here is what the output looks like:

The pseudo class selector in the CSS file indicated that even alternating rows for all <TD> tags would be colored with the background color gray. Also, the position: sticky property in the .header class fixed the position of the header within the viewport.

Method 3: Use the TableEditor tagset

A third method uses the TableEditor tagset, which enables sticky headers to be added by using options. Options are also applied to modify the style for the alternating even and odd rows as well as to have sortable headers.

/* Reference the TableEditor tagset from support.sas.com. */
filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
/* Insert the tagset into the search path for ODS templates. */
ods path(Prepend) work.templat(update);
%include tpl;
ods tagsets.tableeditor file="c:\output\temp.html" 
options(sticky_headers="yes"
sort="yes"
banner_color_even="#e0e0e0") style=htmlblue;
 
proc print data=sashelp.cars;
run;
 
ods tagsets.tableeditor close;

Here is what the output looks like:

In summary, this article describes how easy it is to add sticky headers to tables that are generated by using the ODS HTML destination. Adding fixed headers to any table allows the output to dynamically preserve the headers in the viewable area while scrolling through the table, allowing a much richer experience. Give it a try and let me know how it goes.

Learn More

How to Add Sticky Headers with ODS HTML was published on SAS Users.

10月 162019
 

Introduction

Generating a word cloud (also known as a tag cloud) is a good way to mine internet text. Word (or tag) clouds visually represent the occurrence of keywords found in internet data such as Twitter feeds. In the visual representation, the importance of each keyword is denoted by the font size or font color.

You can easily generate Word clouds by using the Python language. Now that Python has been integrated into the SAS® System (via the SASPy package), you can take advantage of the capabilities of both languages. That is, you create the word cloud with Python. Then you can use SAS to analyze the data and create reports. You must have SAS® 9.4 and Python 3 or later in order to connect to SAS from Python with SASPy. Developed by SAS, SASPy a Python package that contains methods that enable you to connect to SAS from Python and to generate analysis in SAS.

Configuring SASPy

The first step is to configure SASPy. To do so, see the instructions in the SASPy Installation and configuration document. For additional details, see also the SASPy Getting started document and the API Reference document.

Generating a word cloud with Python

The example discussed in this blog uses Python to generate a word cloud by reading an open table from the data.world website that is stored as a CSV file. This file is from a simple Twitter analysis job where contributors commented via tweets as to how they feel about self-driving cars. (For this example, we're using data that are already scored for sentiment. SAS does offer text analytics tools that can score text for sentiment too -- see this example about rating conference presentations.) The sentiments were classified as very positive, slightly positive, neutral, slightly negative, very negative, and not relevant. (In the frequency results that are shown later, these sentiments are specified, respectively, as 1, 2, 3, 4, 5, and not_relevant.) This information is important to automakers as they begin to -design more self-driving vehicles and as transportation companies such as Uber and Lyft are already adding self- driving cars to the road. Along with understanding the sentiments that people expressed, we are also interested in exactly what is contributors said. The word cloud gives you a quick visual representation of both. If you do not have the wordcloud package installed, you need to do that by submitting the following command:

pip install wordcloud

After you install the wordcloud package, you can obtain a list of required and optional parameters by submitting this command:

?wordcloud

Then, follow these steps:

  1. First, you import the packages that you need in Python that enable you to import the CSV file and to create and save the word-cloud image, as shown below.

  2. Create a Python Pandas dataframe from the twitter sentiment data that is stored as CSV data in the data file. (The data in this example is a cleaned-up subset of the original CSV file on the data.world website.)

  3. Use the following code, containing the HEAD() method, to display the first five records of the Sentiment and Text columns. This step enables you to verify that the data was imported correctly.

  4. Create a variable that holds all of the text in a single row of data that can be used in the generation of the word cloud.

  5. Generate the word cloud from the TEXTVAR variable that you create in step 4. Include any parameters that you want. For example, you might want to change the background color from black to white (as shown below) to enable you to see the values better. This step includes the STOPWORDS= parameter, which enables you to supply a list of words that you want to eliminate. If you do not specify a list of words, the parameter uses the built-in default list.

  6. Create the word-cloud image and modify it, as necessary.

Analyzing the data with SAS®

After you create the word cloud, you can further analyze the data in Python. However, you can actually connect to SAS from Python (using the SASPy API package), which enables you to take advantage of SAS software's powerful analytics and reporting capabilities. To see a list of all available APIs, see the API Reference.

The following steps explain how to use SASPy to connect to SAS.

  1. Import the SASPy package (API) . Then create and generate a SAS session, as shown below. The code below creates a SAS session object.

  2. Create a SAS data set from the Python dataframe by using the DATAFRAME2SASDATA method. In the code below, that method is shown as the alias DF2DS.

  3. Use the SUBMIT() method to include SAS code that analyzes the data with the FREQ procedure. The code also uses the GSLIDE procedure to add the word cloud to an Adobe PDF file.

    When you submit the code, SAS generates the PDF file that contains the word-cloud image and a frequency analysis, as shown in the following output:

Summary

As you can see from the responses in the word cloud, it seems that the contributors are quite familiar with Google driverless cars. Some contributors are also familiar with the work that Audi has done in this area. However, you can see that after further analysis (based on a subset of the data), most users are still unsure about this technology. That is, 74 percent of the users responded with a sentiment frequency of 3, which indicates a neutral view about driverless cars. This information should alert automakers that more education and marketing is required before they can bring self-driving cars to market. This analysis should also signal companies such as Uber Technologies Inc. and Lyft, Inc. that perhaps consumers need more information in order to feel secure with such technology.

Creating a word cloud using Python and SAS® software was published on SAS Users.

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

Adding tables and graphics on the same Microsoft Excel worksheet

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

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

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

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

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

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

Output

Adding text and tables to a new worksheet

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

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

Output

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

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

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

Output

Adding multiple tables or graphs on the same worksheet

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

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

Output

Learn more

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

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

Adding tables and graphics on the same Microsoft Excel worksheet

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

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

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

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

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

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

Output

Adding text and tables to a new worksheet

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

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

Output

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

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

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

Output

Adding multiple tables or graphs on the same worksheet

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

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

Output

Learn more

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

3月 222018
 

Generating HTML output might be something that you do daily. After all, HTML is now the default format for Display Manager SAS output, and it is one of the available formats for SAS® Enterprise Guide®. In addition, SAS® Studio generates HTML 5.0 output as a default. The many faces of HTML are also seen during everyday operations, which can include the following:

  • Creating reports for the corporate intranet.
  • Creating a responsive design so that content is displayed well on all devices (including mobile devices).
  • Emailing HTML within the body of an email message.
  • Embedding figures in a web page, making the page easier to send in an email.

These tasks show the need for and the true power and flexibility of HTML. This post shows you how to create HTML outputs for each of these tasks with the Output Delivery System (ODS). Some options to use include the HTML destination (which generates HTML 4.1 output by default) or the HTML5 destination (which generates HTML 5.0 output by default).

Reports

With the HTML destination and PROC REPORT, you can create a summary report that includes drill-down data along with trafficlighting.

   ods html path="c:\temp" file="summary.html";	
 
   proc report data=sashelp.prdsale;
      column Country  Actual Predict; 
      define Country / group;
      define actual / sum;
      define predict / sum;
      compute Country;
         drillvar=cats(country,".html");
         call define(_col_,"url",drillvar);
      endcomp;
   run;
 
   ods html close;
 
   /* Create Detail data */
 
   %macro detail(country);
   ods html path="c:\temp" file="&amp;country..html";
 
   proc report data=sashelp.prdsale(where=(country="&amp;country"));
      column Country region product Predict Actual; 
      compute actual;
         if actual.sum &gt;  predict.sum then 
         call define(_col_,"style","style={background=green}");
   endcomp;
   run;
 
   ods html close;
   %mend;
 
   %detail(CANADA)
   %detail(GERMANY)
%detail(U.S.A.)

Generating HTML output

In This Example

  • The first ODS HTML statement uses a COMPUTE block to create drill-down data for each Country variable. The CALL DEFINE statement within the COMPUTE block uses the URL access method.
  • The second ODS HTML statement creates targets for each of the drill-down values in the summary table by using SAS macro language to subset the data. The filename is based on the value.
  • Trafficlighting is added to the drill-down data. The added color is set to occur within a row when the data value within the Actual Sales column is larger than the data value for the Predicted Sales column.

HTML on Mobile Devices

One approach to generating HTML files is to assume that users access data from mobile devices first. Therefore, each user who accesses a web page on a mobile device should have a good experience. However, the viewport (visible area) is smaller on a mobile device, which often creates a poor viewing experience. Using the VIEWPORT meta tag in the METATEXT= option tells the mobile browser how to size the content that is displayed. In the following output, the content width is set to be the same as the device width, and the  initial-scale property controls the zoom level when the page first loads.

<meta name="viewport" content="width=device-width, initial-scale=1">

 ods html path="C:\temp" file="mobile.html" 
 metatext='name="viewport" content="width=device-width, initial-
 scale=1"';
   proc print data=sashelp.prdsale;
      title "Viewing Output Using Mobile Device";
   run;
   ods html close;

In This Example

  • The HTML destination and the METATEXT= option set the width of the output to the width of the mobile device, and the zoom level for the initial load is set.

HTML within Email

Sending SMTP (HTML) email enables you to send HTML within the body of a message. The body can contain styled output as well as embedded images. To generate HTML within email, you must set the EMAILSYS= option to SMTP, and the EMAILHOST= option must be set to the email server. To generate the email, use a FILENAME statement with the EMAIL access method, along with an HTML destination. You can add an image by using the ATTACH= option along with the INLINED= option to add a content identifier, which is defined in a later TITLE statement. For content to appear properly in the email, the CONTENT_TYPE= option must be set to text/html.

The MSOFFICE2K destination is used here instead of the HTML destination because it holds the style better for non-browser-based applications, like Microsoft Office. The ODSTEXT procedure adds the text to the message body.

   filename mymail email to="chevell.parker@sas.com"
                       subject="Forecast Report"
                       attach=('C:\SAS.png' inlined="logo")
                       content_type="text/html";   
 
   ods msoffice2k file=mymail rs=none style=htmlblue options(pagebreak="no");
     title j=l '<img src="cid:logo" width="120" height="100" />';
     title2 "Report for Company XYZ";
 
 
   proc odstext;
      H3 "Confidential!";
   run;
 
   title;   
   proc print data=sashelp.prdsale;
   run;
 
   ods msoffice2k close;

In This Example

  • The FILENAME statement with the EMAIL access method is used.
  • The ATTACH= option specifies the image to include.
  • The INLINED= option specifies a content identifier.
  • The CONTENT_TYPE= option is text/html for HTML output.
  • The ODSTEXT procedure adds the text before the table.
  • The TITLE statement defines the “logo” content identifier.

Graphics within HTML

The ODS HTML5 destination has many benefits, such as the ability to embed graphics directly in an HTML file (and the default file format is SVG). The ability to embed the figure is helpful when you need to email the HTML file, because the file is self-contained. You can also add a table of contents inline to this file.

ods graphics / height=2.5in width=4in;
ods html5 path="c:\temp" file="html5output.html";
   proc means data=sashelp.prdsale;
   run;
 
   proc sgplot data=sashelp.prdsale;
      vbar product / response=actual;
   run;
 
   ods html5 close;

In This Example

  • The ODS HTML5 statement creates a table along with an embedded figure. The image is stored as an SVG file within the HTML file.

Conclusion

HTML is used in many ways when it comes to reporting. Various ODS destinations can accommodate the specific output that you need.

The many faces of HTML was published on SAS Users.

1月 112018
 

The SAS® platform is now open to be accessed from open-source clients such as Python, Lua, Java, the R language, and REST APIs to leverage the capabilities of SAS® Viya® products and solutions. You can analyze your data in a cloud-enabled environment that handles large amounts of data in a variety of different formats. To find out more about SAS Viya, see the “SAS Viya: What's in it for me? The user.” article.

This blog post focuses on the openness of SAS® 9.4 and discusses features such as the SASPy package and the SAS kernel for Jupyter Notebook and more as clients to SAS. Note: This blog post is relevant for all maintenance releases of SAS 9.4.

SASPy

The SASPy package enables you to connect to and run your analysis from SAS 9.4 using the object-oriented methods and objects from the Python language as well as the Python magic methods. SASPy translates the objects and methods added into the SAS code before executing the code. To use SASPy, you must have SAS 9.4 and Python 3.x or later.
Note: SASPy is an open-source project that encourages your contributions.

After you have completed the installation and configuration of SASPy, you can import the SASPy package as demonstrated below:
Note: I used Jupyter Notebook to run the examples in this blog post.

1.   Import the SASPy package:

Openness of SAS® 9.4

2.   Start a new session. The sas object is created as a result of starting a SAS session using a locally installed version of SAS under Microsoft Windows. After this session is successfully established, the following note is generated:

Adding Data

Now that the SAS session is started, you need to add some data to analyze. This example uses SASPy to read a CSV file that provides census data based on the ZIP Codes in Los Angeles County and create a SASdata object named tabl:

To view the attributes of this SASdata object named tabl, use the PRINT() function below, which shows the libref and the SAS data set name. It shows the results as Pandas, which is the default result output for tables.

Using Methods to Display and Analyze Data

This section provides some examples of how to use different methods to interact with SAS data via SASPy.

Head() Method

After loading the data, you can look at the first few records of the ZIP Code data, which is easy using the familiar head() method in Python. This example uses the head() method on the SASdata object tabl to display the first five records. The output is shown below:

Describe() Method

After verifying that the data is what you expected, you can now analyze the data. To generate a simple summary of the data, use the Python describe() method in conjunction with the index [1:3]. This combination generates a summary of all the numeric fields within the table and displays only the second and third records. The subscript works only when the result is set to Pandas and does not work if set to HTML or Text, which are also valid options.

Teach_me_SAS() Method

The SAS code generated from the object-oriented Python syntax can also be displayed using SASPy with the teach_me_SAS() method. When you set the argument in this method to True, which is done using a Boolean value, the SAS code is displayed without executing the code:

ColumnInfo() Method

In the next cell, use the columnInfo() method to display the information about each variable in the SAS data set. Note: The SAS code is generated as a result of adding the teach_me_SAS() method in the last section:

Submit() Method

Then, use the submit() method to execute the PROC CONTENTS that are displayed in the cell above directly from Python. The submit method returns a dictionary with two keys, LST and LOG. The LST key contains the results and the LOG key returns the SAS log. The results are displayed as HTML. The HTML package is imported  to display the results.

The SAS Kernel Using Jupyter Notebook

Jupyter Notebook can run programs in various programming languages including SAS when you install and configure the SAS kernel. Using the SAS kernel is another way to run SAS interactively using a web-based program, which also enables you to save the analysis in a notebook. See the links above for details about installation and configuration of the SAS kernel. To verify that the SAS kernel installed successfully, you can run the following code: jupyter kernelspec list

From the command line, use the following command to start the Jupyter Notebook: Jupyter notebook. The screenshot below shows the Jupyter Notebook session that starts when you run the code. To execute SAS syntax from Jupyter Notebook, select SAS from the New drop-down list as shown below:

You can add SAS code to a cell in Jupyter Notebook and execute it. The following code adds a PRINT procedure and a SGPLOT procedure. The output is in HTML5 by default. However, you can specify a different output format if needed.

You can also use magics in the cell such as the %%python magic even though you are using the SAS kernel. You can do this for any kernel that you have installed.

Other SAS Goodness

There are more ways of interacting with other languages with SAS as well. For example, you can use the Groovy procedure to run Groovy statements on the Java Virtual Machine (JVM). You can also use the LUA procedure to run LUA code from SAS along with the ability to call most SAS functions from Lua. For more information, see “Using Lua within your SAS programs.” Another very powerful feature is the DATA step JavaObject, which provides the ability to instantiate Java classes and access fields and methods. The DATA step JavaObject has been available since SAS® 9.2.

Resources

SASPy Documentation

Introducing SASPy: Use Python code to access SAS

Come on in, we're open: The openness of SAS® 9.4 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.

6月 242016
 

ProblemSolversXML has become one of the major standards for moving data across the Internet. Some of XML’s strengths are the abilities to better describe data and to be more extensible than any of its predecessors such as CSV. Due to the increased popularity of XML for moving data, I provide a few tips in this article that will help when you need to read XML files into SAS software.

Reading XML Files

You can read XML files into SAS using either the XML engine or the XMLV2 engine. The XML engine was the first engine that SAS created to read XML files. The XMLV2 engine includes new functionality and enhancements and is aliased to the XML92 engine in SAS® 9.2.

It is easy to read XML files using the XMLV2 engine when the XML file that you read uses the GENERIC markup type and conforms to a very rectangular definition. Here is an example of the XML file layout that is required to be read natively using the XMLV2 engine:

If the file is not in this format, the following informative error message is generated in the SAS log:

reading XML files into SAS® software

If the file is not in this format, the following informative error message is generated in the SAS log:

Reading XML files into SAS Software02

The XMLMap file referenced in the error message is a SAS specific file that instructs the engine about how to create rows, columns, and other information from the XML markup. You can use either of the following methods to generate the XMLMap file:

  • the AUTOMAP= option within the SAS session beginning with SAS® 9.3 TS1M2
  • the SAS XML Mapper, which is a stand-alone Java application

Generating Dynamic Maps within the SAS Session

Using the XMLV2 engine, you can create a dynamic XMLMap file and use it to generate SAS data sets by specifying the AUTOMAP= and the XMLMAP= options in the LIBNAME statement. The AUTOMAP= option has two possible values: REPLACE and REUSE. REPLACE updates an existing XMLMap file, whereas REUSE uses an existing XMLMap file. Both values create a new XMLMap file if one does not currently exist. When the file is automapped, one or more data sets is created. This method creates a representation that is as relational as possible based on the XML markup. In addition, it generates surrogate keys that enable you to combine the data using the SQL procedure or the DATA step. The following example demonstrates this method.

Here is the XML data that is read:

Reading XML files into SAS Software03

Here is the SAS session code:

filename datafile 'c:teststudents2.xml';
filename mapfile "c:teststudents2.map";
libname datafile xmlv2 xmlmap=mapfile automap=replace;

proc copy in=datafile out=work;
run;

Here is the output:

Reading XML files into SAS Software04

For more information about using the XMLV2 engine, refer to the “SAS® 9.4 XMLV2 LIBNAME Engine Tip Sheet.”

Using SAS XML Mapper

If you answer “yes” to the following questions, you should use SAS XML Mapper to generate the XMLMap file rather than generating this file dynamically within the SAS session:

  • Do you need to generate an XMLMap from an XML schema file?
  • Do you need to generate a custom XMLMap file or map only a portion of the file?
  • Do you need to view the data to be imported before reading the XML file?
  • Do you need to add specific metadata such as data types, informats/formats, column widths, or names?
  • Do you want to generate sample code for the import of the XML file?

The following image shows SAS XML Mapper once you click the icon that runs the automapping feature, which enables you to quickly generate tables from the XML markup:

Reading XML files into SAS Software05

Using SAS XML Mapper, you can also generate a custom XMLMap file.

For more information, refer to the following video tutorials about creating XMLMap files:

I hope this article helps you easily read XML files into SAS regardless of the file structure. This information should also help you determine the appropriate method to use when generating an XMLMap file.

tags: SAS Problem Solvers, SAS Programmers

Tips for reading XML files into SAS® software was published on SAS Users.