Business Intelligence Using SAS

SAS Technical topics


Leave a comment

SAS Report Linking by chaining SAS Stored Processes

Reports are the end product of your database. They are the formatted result of database queries and contain useful data for decision-making and analysis. Many time the viewer of data needs to drill deeper into the details of a report to see what makes up its numbers.

SAS provides users with several methods of presenting dynamic drill down Reports. The technique we will be exploring here involves some report linking using the power of the SAS (9.4) Stored Processes Web applications.

Report linking in SAS

Tables, graphs, Titles, Subtitles and FOOTNOTEs in specific report can be linked by creating Hyperlinks. SAS can create these links almost anywhere that text is displayed, as well as within graphic objects. Links can be established between portions of tables, graphs, other locations within a table, and between tables with different types. Within a table links can be established within data values, formats, titles, header text, and graphic symbols.

 

Chaining Stored processes

A stored process is a SAS program that is stored on a server and defined in Metadata. This Stored Process can then be called and executed as required by requesting applications. You can use stored processes for Web reporting, analytics, building Web applications, delivering packages to clients or to the middle tier, and publishing results to channels or repositories. Stored processes can also access any SAS data source or external file and create new data sets, files, or other data targets supported by SAS.

It is possible to create even more flexible Web applications by linking together stored process Web applications. Thus you can create a first stored process for generating the first Web page that can calls a second Stored Process for generating the second Web page, which can call a third stored process, and so on. This technique enables us to distribute processing and decision making across a number of programs and user interfaces, making it possible to develop complex systems.

There is several methods to connect the stored processes, see the SAS® 9.4 Stored Processes Developer’s Guidefor more detailed informations.

 

Understanding Hyperlinks

Hyperlink is a link from a webpage or document to another location or file, typically activated by clicking on the highlighted word or image on the screen.

Hyperlinking your text is done by placing the text within the HTML hyperlink tags, <a> and </a>. The most important attribute of the <a> element is the HREF attribute which dictates the link’s destination. You can also add a title attribute to display text while the user’s mouse hovers over the link. Here is the SYNTAX for Hyperlinking Text:

HREF

The more useful and often overlooked use of hyperlinks, however, is to use them within Tables and Graphs for drilling down.

Linked Report using SAS Stored Processes

Linking between different reports (or Chaining Reports) is a matter of building a URL string that link to a detailed report. Let’s take a look at the following sample example of a linked Report that Summarizes the SAShelp.PRDSALE. The example is sample to just get the concept across. It is constructed so that clicking on the Country name links to a Detailed Report regarding the selected Country as shown in the following figure:

Report0

 

This is useful when Sales Analyst want to link each row from the summary report to retrieve detailed results for a given Country.

We will show here how this task can be accomplished by generating Hyperlinks between two stored Processes. The trick is to create a hyperlink in the parent Stored Process (that generate the Summary Report) and pass the variable information through the URL address to the child Stored Process (that generate the Detailed report).

 

Creating Stored Processes using SAS Enterprise Guide

 

When creating a Stored Process it is often easiest to use Enterprise Guide, since you can use wizards to create code or write your own, test it out and then save the code as a stored process. A wizard will guide you through the process and allow you to specify everything in an easy way.

 

Prerequisites

To create the Stored Process for our example, you need the following:

  • You SAS administrator must provide proper permissions in the metadata and access to appropriate folder to save the Stored Process.
  • SAS Enterprise Guide access to create and register the Stored Process.
  • Basics understanding of the SAS programming language.

 

Creating the Detailed Stored Process

Launch the Stored Process Manager from Enterprise Guide, and create the following Stored Process, called SP_LinkedReport_Country_Detail, that uses the country value to query the SAShelp.PRDSALE dataset and returns the Detailed Actual and Predict Sales Report regarding the selected country, as follow:

Report1

As shown above, use Hide from User checkbox to hide this Stored Process from users to ensure the Detailed Report is only available from the Summary Report. This option hides the stored process from the folder view and search results for clients like the SAS Stored Process Web Application, so the user won’t try to execute the second or subsequent stored processes in a chain directly.

 

Add the SAS Code

The stored process can be stored on the metadata server and contains the following code:

Code1

title1 “SAShelp.PRDSALE Dataset “;
title2 “Detailed Actual and Predict Sales regarding &country.”;
title3 “By Region, Division, Product type and Product name”;
title4 height=2 “Situation on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))”;
/* Get the Data */
data prdsale (drop=Country);
/* This will allow to correctly display the Country
‘Total’ in the Proc Report without truncating */
attrib CountryName format=$50. ;
Set sashelp.prdsale;
CountryName = Country ;
run;
/* Present the Data */
proc report data=prdsale (Where = (upcase(Trim(CountryName )) =Upcase(“&country”))) headskip split=’/’
style(REPORT)={background=black}style(HEADER)={foreground=blue font_weight=bold};
column CountryName  region division prodtype product  Year ,(actual  predict) ;
define CountryName / group “country” width=20 order=internal;
define region / group “region” width=20 ;
define division / group “division” width=20 ;
define prodtype / group “prodtype” width=20 ;
define product / group “product” width=20 ;
define Year / across “Year” width=20 ;
define actual / analysis  “actual” width=20 ;
define predict / analysis  “predict” width=20 ;
break after CountryName / summarize skip ol;
compute CountryName ;
if _break_ = ‘CountryName’ then CountryName=trim(CountryName)||” Total”;
endcomp;
run;

 

Execution Options

This window allows you to indicate where the stored process code is stored on the server, how code is run, and how you want the results.

Report3

  • In the server type option, choose Default Server to allow the client application to run the Stored Process on the default server type. In this case, the stored process server is used unless the client application specifies to use the workspace server.
  • Specifies whether the stored process can be executed on other application servers or only on the selected application server, and where the source code is stored. If you allow the stored process to be executed on other application servers, then the source code is stored in metadata. If you allow execution on the selected application server only, then you must specify whether the source code is stored in metadata or on the application server. If the source code is stored on the application server, then you must specify the source code repository and source file.
  • Select the Stream check box to specify the Result capabilities for the Stored Process. The streaming Output is needed to stream the results back to the browser.
  • Click Next to go to the Prompt windows.

Creating the Prompt

The next screen is important. One of the main advantages of using Stored Processes is the ability to specify the value of macro parameters at run time.

Select New > Prompt from SAS Code for > country (macro variable). As you can see below the Wizard look through your SAS program and suggest any macro variables that you may wish to use as run-time prompts:

Report4

 

In the Edit Prompt, do the following:

  • The Name value must match the macro variable “country” in the code.
  • In the Displayed Text field describe what you want the user to do, such as Type a country name or Select a value.
  • Select a “Requires a non-blank value” check box to ensure the user provides an answer. Otherwise, the code will not be able to complete and the stored process will fail.
  • In the Prompt Type and Values tab, no changes are required since this is a text value.

Report5

You can skip step 5 and continue to step 6. Use this window to review the stored process settings. Click Finish to complete the metadata registration.

Test the Stored Process

You can test the Stored Process using SAS Enterprise Guide.

Report6

The warning appears because we execute the Stored Process with streaming output from Enterprise Guide using the Workspace Server. Starting with 9.3, the workspace server supports streaming output and web services, and the warning is for missing static images in streaming output. In this case SAS Enterprise Guide advise us to make of the following changes:

  • Change the graph format to ActiveX or Java.
  • Change the result format to PDF or RTF
  • Update the stored process to produce package output.

You can ignore the Warning Message and click OK, and the detailed report is displayed as follow:

Report7

Running the Stored Process from the Stored Process Web Application

It’s interesting to look at the URL that has been generated when executing the detailed Stored Process from the Stored Process Web Application to understand the construction of the hyperlinking (for the next steps):

Report10

The URL specifies your server name and port, an absolute path to your Stored Process Web Application, and the query string (following the question mark character). Each name in the query string is separated from the following value by an equal sign (=). The Country name/value pairs is separated by ampersand characters (&). This will help us to build the hyperlink in the next steps.

Remark: You must first deselect the “Hide from User” checkbox (as explained above) to execute the Stored Process from the Web Stored Process.

Creating the Summary Stored Process

The summary Stored Process can be created using SAS Enterprise Guide as explained earlier. For the SAS Stored Process code, let’s see now how we can generate the hyperlink in this Summary Stored Process that will pass the variable information through the URL address to the detailed Stored Process.

Method 1: Generating hyperlinks using PROC REPORT

Generating hyperlinks are easy using PROC REPORT. Unique to PROC REPORT, you can use the URL attribute in the CALL DEFINE statement in the compute block associate the URL with the COUTRY as follow:

%let SP_name = SP_LinkedReport_Country_Detail ;

title1″SAShelp.PRDSALE Dataset”;

title2″Actual and Predict Sales Summary with URL link within Proc REPORT”;

title3″By Country and Region”;

title4height=2“Situation on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at

%TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))”;

footnote justify=left  height=2 Link=”http://support.sas.com/documentation/cdl/en/stpug/64882/PDF/default/stpug.pdf&#8221;

‘Go to the SAS® 9.4 Stored Processes Developer”s Guide pdf documentation.’;

/* Get the Data */

data prdsale (drop=Country);

/* This will allow to correctly display the Country

‘Total’ in the Proc Report without truncating */

attrib CountryName format=$50. ;

Set sashelp.prdsale;

CountryName = Country ;

run;

/* Present the Data */

proc reportdata=prdsale headskipsplit=’/’

style(REPORT)={background=black}style(HEADER)={foreground=blue font_weight=bold};

column CountryName region Year ,(actual predict) ;

define CountryName / group”Country”width=50order=internal;

define region / group”region”width=20 ;

define Year / across”Year”width=20 ;

define actual / analysis”actual”width=20 ;

define predict / analysis”predict”width=20 ;

breakafter CountryName / summarizeskipol;

compute CountryName / charLength=200;

X=CountryName;

urlstring = ‘/SASStoredProcess/do?&_program=’ ||

TRANWRD(strip(“&_metafolder”),’ ‘,’+’) || “&SP_name.”

|| ‘&Country=’ || strip(X);

calldefine(_col_, “URL”, urlstring);

if _break_ = ‘CountryName’then CountryName=trim(CountryName)||” Total”;

endcomp;

run;

The code that creates URLstring (as CountryName) appears long and convoluted. Here are explanations to understand it:

  • Hyperlinking is done using the URL attribute in the CALL DEFINE statement in the compute block.
  • The _program reserved macro variable references our detail stored process which will perform the analysis and build the report.
  • Within compute statement, the reference to the detailed Stored Process is done as a relative instead of a full URL. This provides the ability to move the stored process from a development environment easily to the production environment.
  • We take the advantage of the reserved macro variable %_metafolder that contains the name or path of the folder for the stored process that is being executed. Thus, when promoting the Stored Process (from development to production for example), or if the folder name or path change no changes required for the stored Process code.
  • There are special rules for the formatting of name/value pairs in a URL. Special characters (such as most punctuation characters, including spaces) in a value must be URL-encoded. Spaces can be encoded as a plus sign (+) or %20. The TRANWRD function is used to ensure that the space in the Stored Process path name is encoded as a plus sign (+). Other characters must be encoded using the %nn convention, where nn is the hexadecimal representation of the character in the ASCII character set.
  • In the TITLE and FOOTNOTE statements the LINK= option can be used to specify the link you want to add to your Report. The option can point to an internal anchor, a local file, or may even contain a fully qualified path. In our example the LINK= option is used in FOOTNOTE to SAS® 9.4 Stored Processes Developer’s Guide pdf documentation.

 

Method 2: Generating hyperlinks for each record in the Dataset

Another way to generate hyperlinks is to build the URL path for each record in the Dataset, in this way when the summary report appears in the Web browser, the HTML tag appears as hyperlink. Here is the SAS Stored Process code to use:

%let SP_name = SP_LinkedReport_Country_Detail;
title1 “SAShelp.PRDSALE Dataset”;
title2 “Actual and Predict Sales Summary with URL link within Proc REPORT”;
title3 “By Country and Region”;
title4 height=2 “Situation on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at
%TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))”;
footnote justify=left height=2 Link=”http://support.sas.com/documentation/cdl/en/stpug/64882/PDF/default/stpug.pdf&#8221;
‘Go to the SAS® 9.4 Stored Processes Developer”s Guide pdf documentation.’;

/* Get the Data */
Proc sql ;
create table prdsale as
Select
“<A HREF=/SASStoredProcess/do?_program=”
|| TRANWRD(strip(“&_metafolder”),’ ‘,’+’) || “&SP_name.”
|| ‘&Country=’|| Country
|| “target=_blank>” || Country ||”</a>”
as CountryName length=500 format=$500.
,region,Year ,
sum(actual) as actual  , sum(predict) as predict
From sashelp.prdsale
Group by Country,  region ,  Year;
quit;
/* Present the Data */
proc report data=prdsale  headskip split=’/’;
column CountryName region  Year ,(actual  predict) ;
define CountryName / group “Country” width=50 order=internal;
define region / group “region” width=20 ;
define Year / across “Year” width=20 ;
define actual / analysis  “actual” width=20 ;
define predict / analysis  “predict” width=20 ;
break after CountryName / summarize skip ol;
run;

Explanations regarding the code above:

  • Hyperlinking is done by placing the text within the HTML hyperlink tags, <a> and </a>. The most important attribute of the <a> element is the HREF attribute which dictates the link’s destination.
  • The _program reserved macro variable references our detail stored process which will perform the analysis and build the report.
  • We take the advantage of the reserved macro variable %_metafolder that contains the name or path of the folder for the stored process that is being executed. This will mean that if the stored process name changes or the stored process is moved to another place (from development to production for example in the metadata then it will still work as expected.
  • There are special rules for the formatting of name/value pairs in a URL. Special characters (such as most punctuation characters, including spaces) in a value must be URL-encoded. Spaces can be encoded as a plus sign (+) or %20. The TRANWRD function is used to ensures that the space in the Stored Process path name is encoded as a plus sign (+). Other characters are encoded using the %nn convention, where nn is the hexadecimal representation of the character in the ASCII character set.
  • The CountryName is given a large length and format because the paths can become very long. This ensures that the hyperlink is not truncated, which causes the link to not work.
  • TARGET controls where the new document will be displayed when the user follows a link. Most of the time, clicking on a link simply loads a new document in the same window where the link was. However, with TARGET, you can have the new document open in a new window, or if you are using frames, in another frame. TARGET also has four predefined values, which can be used as if certain windows and frames already have names without you having to assign them (_blank: opens the linked document in a new window or tab, _self: opens the linked document in the same frame as it was clicked (this is default), _parent: opens the linked document in the parent frame, and _top:     opens the linked document in the full body of the window).

 

URL path method Limitations

The URL path method has limitations. Some Web browsers and Web servers might impose a limit on the total length of a URL. URLs with many parameters values that exceed this limit can be truncated without warning, which results in incomplete or inconsistent input data for your Stored Process. URL length limits are not well documented and might require experimentation with your particular configuration.

There are ways around this limitation, using Custom Input Forms for example, which are not recovered here.

Linking from SAS Graphs

Most Graphics elements can be created so that the viewer can click on them and then drill-down to another graph or report as shown in the following figure:

Report11

Linkable graphic elements include, but are not limited to, histogram bars, scatter plot lines and symbols, maps, pie slices, and legend elements. Creating the URLLink can be done in the same way as the previous example. PROC GRAPH has an HTML option available that can be used with the URLLink variable.

Create a new Stored Process (using SAS Enterprise Guide or SAS Management Console) in the same way as the previous Stored Process (with streaming output), and add the following SAS Stored Process code:

%let SP_name = SP_LinkedReport_Country_Detail;
title1 “SAShelp.PRDSALE Dataset”;
title2 “Actual Sales Summary with URL link within Proc GCHART”;
title3 “By Country and Region”;
title4 height=3 “Situation on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at
%TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))”;
footnote justify=left height=3 Link=”http://support.sas.com/documentation/cdl/en/stpug/64882/PDF/default/stpug.pdf&#8221;
‘Go to the SAS® 9.4 Stored Processes Developer”s Guide pdf documentation.’;

/* Get the Data with the URLLink */
Proc sql ;
create table prdsale as
Select Country,
“<A HREF=/SASStoredProcess/do?_program=”
|| TRANWRD(strip(“&_metafolder”),’ ‘,’+’) || “&SP_name.”
|| ‘&Country=’|| Country
|| “target=_blank>” || Country ||”</a>”
as URLLink length=500 format=$500.
,region,Year ,
sum(actual) as actual  , sum(predict) as predict
From sashelp.prdsale
Group by Country,  region ,  Year
;
quit;
/* Generate 3D horizontal bar chart  */
proc gchart DATA=prdsale;
vbar3d region / sumvar=actual subgroup=Country SUMVAR=actual html=URLLink raxis=axis1 shape=cylinder ;
run;
quit;

 

Enhancing your Stored Process

There are many ways that you can customize your SAS Stored Processes appearance to produce higher quality Graphs and Reports to impress your customers. This can be done by using some several reserved macro provided by SAS available for your use with Stored Processes such as ODS destination, ODS Styles, and Graphics Device. This reserved macro can be set from a Prompt so that the user can decide at run time how report should appear. Adding this Prompt does not require any code changes. One easy way to achieve this is to use shared prompt provided by SAS.

 

Understanding Shared prompts

A shared prompt is a prompt that is stored in a shared location and that can be accessed by multiple users, applications, and software features. Sharing prompts is helpful when that prompt is complex or when you might need to reuse that prompt (perhaps in other applications or contexts). The following examples are good candidates for sharing:

  • dynamic prompts with complex configurations.
  • sets of cascaded prompts.
  • groups of prompts that are often reused (like chart options).

Changing the Appearance of Your Output

You can enhance your SAS Stored Processes web applications using _odsstyle to choose different ODS styles which control colours, fonts and so on. So you can add a dropdown with a select tag for _odsstyle. Then if you select seaside and run it, then it will produce output using that style. This is because it will have passed _odsstyle=seaside to the stored process.

Let’s create the new _odsstyle Prompt in the previous Stored Process using SAS Enterprise Guide.

From SAS Enterprise Guide, select the previous Stored Process to modify and in the left -hand pane, select Prompts. Then click Sharing > Add Shared… as follow:

Report12

Add one of ODS styles Prompts, such as ODS Styles-Static:

Report13

To stop Sharing the Prompt you just add, select the Prompt from the list of Input Prompts then select Unshare. Then SAS copies the Unshared prompt to the Stored Process. Click “Yes” in the confirmation message to confirm that is what you want to do. Thus you can customize the prompt to meet your needs without affecting others users at your site.

Report14

Run the Stored Process to view the change.

Report15

 

Changing the format of your Output

You can use _odsdest to produce output in various formats, rather than the default HTML format. You could add a dropdown for _odsdest to your web page. You can use the HTML select tag to make this. Then you could run it by selecting the PDF(Portable Document Format) output for example. That would call the stored process passing _odsdest=pdf to it.

You can add the Shared Prompt “ODS Output Format-Static” in the same way in the previous Shared Prompt to produce output in various formats:

Report16

Run the Stored Process to view the change.

 

 

ASSEM Redouan

SAS BI Consultant

assemredouan@hotmail.com


1 Comment

Interactive Charts and Graphs using SAS Stored Process and JavaScript libraries

Charts and Graphs are used to simplify large amounts of information into easy-to-understand formats that clearly and effectively communicate important points. Data visualization tools using JavaScript have been flourishing recently. There are hundreds of phenomenal JavaScript interactive Charts and Graphs that greatly simplify the visualization of data that one can use to avoid writing everything from scratch.

XXXXXXXXXXX

In this article I will show you (using SAS 9.4) how you can harness the power of the SAS® Stored Processes to impress your customers by generating a higher quality interactive Charts and Graphs using one of the useful JavaScript libraries with little or no HTML /JavaScript programming experience.

SAS Stored Processes

One of the major benefits of using SAS Stored Processes is extensibility. SAS stored processes are one of the most customizable products. They can be used for creating web reports, performing analytics, building web applications, delivering packages to clients or to the middle tier, and publishing results to channels or repositories. Stored processes can also access any SAS data source or external file and create new data sets, files, or other data targets that are supported by SAS.

Stored processes can be used with a variety of clients, including, but not limited to, the following:

  • SAS Enterprise Guide
  • SAS Stored Process Web Application
  • SAS Add-In for Microsoft Office
  • SAS Data Integration Studio
  • JMP
  • SAS BI Dashboard
  • SAS Information Map Studio
  • SAS Web Report Studio
  • SAS Information Delivery Portal

 

SAS Stored Process Web Application

One of the strengths of SAS Stored Process is that they can be executed from SAS Stored Process Web Application. Running an SAS Stored process Web Application allows users to execute powerful SAS procedures without knowing SAS, without having SAS on their desktop, and without a SAS license; the only need permission to access the information. This provide additional security for your data, because the application that access the data are stored in a central, secure location.

Using JavaScript charts and graphs libraries

There are many JavaScript charts and graphs libraries (or frameworks) available on the internet, some online, some desktop-based, some free, and some paid. They provide a collection of pre-written JavaScript controls which allow us to build web applications much quicker than we could otherwise do so. We also need much less knowledge to make use of these frameworks than we would to write the functionality from scratch.

Here are 40 Free JavaScript chart and graph libraries for developers that you will find very useful.

Highcharts JavaScript Library

Highcharts is one of the most extensive library of charts and graphs using HTML5/JavaScript that works on just about any device or browser offering an easy way of adding interactive charts to your web site or web application. Highcharts currently supports line, spline, area, areaspline, column, bar, pie and scatter chart types. Here is the link to the Demo and Download:

Highcharts Demo

Highcharts Download

Highcharts is a dynamic plugin because you can add, remove and modify series, axes or points at any time after chart creation and you can load data from external files; tooltip labels are also supported which is great for detailed information in a point of a chart; zooming and last but not least all text labels can be rotated in any angle.

Let take a look, for our demo, at the following Area charts “Demo – Basic Line” example from Highcharts:

Pho1png

 

You can easily dynamically remove or add series, view detailed information in a point of a chart, Print chart as (PNG, JPEG and SVG vector) image or PDF document with a higher quality as shown below:

Pho2png

The sample data used for our demo

Here is the data used for the above “Demo – Basic line” example from the WorldClimate.com.

data temperature ;
Infile datalines delimiter=’,’;
Input City $ Month Temperature ;
Datalines;
Tokyo,19359,7
Tokyo,19390,6.9
Tokyo,19418,9.5
Tokyo,19449,14.5
Tokyo,19479,18.2
Tokyo,19510,21.5
Tokyo,19540,25.2
Tokyo,19571,26.5
Tokyo,19602,23.3
Tokyo,19632,18.3
Tokyo,19663,13.9
Tokyo,19693,9.6
New York,19359,-0.2
New York,19390,0.8
New York,19418,5.7
New York,19449,11.3
New York,19479,17
New York,19510,22
New York,19540,24.8
New York,19571,24.1
New York,19602,20.1
New York,19632,14.1
New York,19663,8.6
New York,19693,2.5
Berlin,19359,-0.9
Berlin,19390,0.6
Berlin,19418,3.5
Berlin,19449,8.4
Berlin,19479,13.5
Berlin,19510,17
Berlin,19540,18.6
Berlin,19571,17.9
Berlin,19602,14.3
Berlin,19632,9
Berlin,19663,3.9
Berlin,19693,1
London,19359,3.9
London,19390,4.2
London,19418,5.7
London,19449,8.5
London,19479,11.9
London,19510,15.2
London,19540,17
London,19571,16.6
London,19602,14.2
London,19632,10.3
London,19663,6.6
London,19693,4.8
;

Getting the HTML with JavaScript embedded for our example

 

Using Mozilla Firefox, click the EDIT IN JSFIDDLE to open up the JSFIDDLE interface:

Pho8png

 

The main section of the site is divided into four areas:

  • CSS: CSS to be applied to the HTML.
  • HTML: The HTML to be used.
  • JavaScript: JavaScript source code
  • Result: The results of executing the CSS, HTML, and JavaScript entered into the sections of the page.

Right-Click in the Result area and click This Frame à View Frame Source as follow:

Pho3png

 

The View Frame Source that contains the HTML (with JavaScript embedded) must be as follow:

Pho4png

Copy the resulting HTML to use within the Stored Process that we will create in the next steps.

Creating the SAS Stored Process

When creating a Stored Process it is often easiest to use Enterprise Guide, since you can use wizards to create code or write your own, test it out and then save the code as a stored process. A wizard will guide you through the process and allow you to specify everything in an easy way.

Prerequisites

To create the Stored Process for our example, you need the following:

  • You SAS administrator must provide proper permissions in the metadata and access to appropriate folder to save the Stored Process.
  • SAS Enterprise Guide access to create and register the Stored Process.
  • Basics understanding of the SAS programming language.

Creating Stored Processes using Enterprise Guide

Launch the Stored Process Manager from Enterprise Guide :

Phop10ng

Name and Description

Give a name, location and description for the Stored Process, for example:

Phop16ng

SAS Code

Add the SAS Stored Process code as follow:

data _null_ ;
input ;
file _webout ;
put _infile_ ;
cards4 ;

Paste the resulting HTML (with JavaScript embedded)  here !!
;;;;
run ;

 

Phop13ng

One of the options when creating the stored process is “Include code for “. You must deselect “Stored process macros” as shown in the above figure. If you allow the stored process to be built with stored process macros, SAS puts code before and after your code. This SAS – added code uses “_webout” so you can’t use this device. When you deselect “Stored process macros” SAS does not put this code around your code and you are free to use “_webout “. The option “LIBNAME references” is also not needed for our example.

Note: Running this Stored Process in SAS Enterprise Guide® will result in an error, but the same Stored Process will work on the SAS Stored Process Web Application. This is because SAS Enterprise Guide® does not understand _webout. This means, of course, that you must test your code on the SAS Stored Process Web Application.

Execution Options

Phop11ng

In the server type option, choose Default Server to allow the client application to run the Stored Process on the default server type. In this case, the stored process server is used unless the client application specifies to use the workspace server.

Specifies whether the stored process can be executed on other application servers or only on the selected application server, and where the source code is stored. If you allow the stored process to be executed on other application servers, then the source code is stored in metadata. If you allow execution on the selected application server only, then you must specify whether the source code is stored in metadata or on the application server. If the source code is stored on the application server, then you must specify the source code repository and source file.

Select the Stream check box to specify the Result capabilities for the Stored Process. The streaming Output is needed to stream the results back to the browser.

Click Next to go to the Summary page, de-select Run stored process when finished and click Finish.

Your stored process is now created and ready to be tested from the SAS Stored Process Web Application.

Test the Stored Process from the SAS Stored Process Web Application

Logon to the SAS Web Server Stored Process Application, which is in a location similar to the following address: http://YourServername:PortNumber/SASStoredProcess/do.

Note that this address is case sensitive.

Phop14ng

Next you will see the default SAS Stored Process Web Application home page. Your organization might have already made change to the page, so it might appear differently.

Phop15ng

Navigate to the Stored Process that you just created, click the name of the Stored Process, and the Highcharts “Demo – Basic Line” will display.

Generating data needed by the Chart

As the needed data by the chart (categories and series) is hard-coded in the JavaScript, the next challenge is to find a way to generate dynamically the source data.

Pho5png

 

One Solution

We need macros to generate the lines (categories and series) as needed by the chart for any input source dataset. For this purpose I wrote a macro that convert the source (temperature) data into the required format using macro variable and then inject the macro variables in the stored process after making some changes in the HTML insight the SAS code for the Stored Process.

More specifically, the think is to generate two macro variables that store the data as follow:

series =

name: ‘Berlin’, data: [ -0.9, 0.6, 3.5, 8.4, 13.5, 17, 18.6, 17.9, 14.3, 9, 3.9, 1]}, {

name: ‘London’, data: [ 3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17, 16.6, 14.2, 10.3, 6.6, 4.8]}, {

name: ‘New York’, data: [ -0.2, 0.8, 5.7, 11.3, 17, 22, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]}, {

name: ‘Tokyo’, data: [ 7, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6]

Categories = [‘JAN13′,’FEB13′,’MAR13′,’APR13′,’MAY13′,’JUN13′,’JUL13′,’AUG13′,’SEP13′,’OCT13′,’NOV13′,’DEC13’]

The generated output table is as follow:

JS_Temperature

And then inject the two macro variables in the SAS code.

First Method : PUT statement and Macro facility

You can use the %PUT statement as follow:

data_null_;

file _webout;

Put” Fist line of the HTML“;

Put” Second line of the HTML”;

…….

/* Macro variable that contains your Graph Title*/

Put”                 text: ‘&Graphtitle’,”;

…..

/* Macro variable that contains your Graph SubTitle*/

Put”                 text: ‘&Graphsubtitle’,”;

….

/* Macro variable that contains Categories*/

Put”                 categories: &Categories “;

/* Macro variable that contains your Y Axis Title Text*/

Put”                     text: ‘&yAxistitletext'”;

….

Put”&series”; /* Macro variable that contains series*/

Put” </html>”;

Put” “;

run;

Macro that generate the data needed by the Chart

Here is the full generic SAS code and how is used within SAS Stored Process:

*Choose here your Graph title, subtitle and Y Axis Title;

%Let Graphtitle = My Graph Title ;

%Let Graphsubtitle = My Graph Subtitle;

%Let yAxistitletext = Y Axis Title Text ;

*Generic SAS code that will convert the source data into the required format needed by the HTML. ;

%Global series Categories;

OptionsnoQuoteLenMax;

* To avoid warning message showing that the string is more than 262 characters when you store a string of more than 262 characters into a macro varaible.;

%macro GetDataForDemoBasicline;
%global series Categories ;
Proc sql noprint ;
Create table temp1 as
Select distinct City , month, “‘” || put(month , monyy.)|| “‘” as MonYY,
cats(Temperature) as Temperature
From Temperature
order by City , month;
Quit;

Data temp2;
Attrib endcar length=$10. ;
set temp1 end=eof;
if eof then endcar = ” “; else endcar =” }, {“;
Run;

Data JS_Temperature (keep=City Month_concat Temperature_concat);
Set temp2;
By City Month;
Length Month_con Temperature_con Month_concat Temperature_concat $150.;
Retain Month_con Temperature_con ;
If first.City and first.Month then do;
Month_con = trim(MonYY);
Temperature_con = trim(Temperature);
End;
Else do;
Month_con = trim(Month_con) || “,” || trim(MonYY);
Temperature_con = trim(Temperature_con) || “, ” || trim(Temperature);
IF not (last.first.City and first.Month) then do;
Month_concat = “[” || Strip(Substr(Month_con,1))|| “]”;
Temperature_concat=”name: ‘” || strip(City) || “‘, data: [ ” || Strip(Substr(Temperature_con,1))|| “]”|| strip(endcar) ;
End;
Else do;
Month_concat = “[” || Strip(Substr(Month_con,1))|| “]”;
Temperature_concat=”name: ‘” || strip(City) || “‘, data: [ ” || Strip(Substr(Temperature_con,1)) || “]” ;
End;
End;
If Last.City then output;
run;
*Generating macro variable for series;
Proc sql noprint;
Select Trim(Temperature_concat) into: series
separated by ‘ ‘
from JS_Temperature ;
Quit;
*Generating macro variable for Categories;
Proc sql noprint;
Select distinct Trim(Month_concat) into: Categories
separated by ‘ ‘
from JS_Temperature ;
Quit;
*Deleting work tables;
proc datasets lib=work nolist nowarn memtype=(data);
delete Temperature temp1 temp2 ;
quit;
%MEND  GetDataForDemoBasicline;
%GetDataForDemoBasicline;

*And now  inject the two macro variables in the SAS code, using the %PUT statement.;

data_null_;
file _webout;
Put” <!–DOCTYPE HTML>”;
Put” <html>”;
Put”     <head>”;
Put”           <meta http-equiv=””Content-Type”” content=””text/html; charset=utf-8″”>”;
Put”          <span class=”hiddenSpellError” pre=”” data-mce-bogus=”1″>Highcharts</span> Example”;
Put” “;
Put”           “;
Put”           “;
Put” $(function () {“;
Put”         $(‘#container’).highcharts({“;
Put”             title: {“;
/* Macro variable that contains your Graph Title*/
Put”                 text: ‘&Graphtitle’,”;
Put”                 x: -20 //center”;
Put”             },”;
Put”             subtitle: {“;
/* Macro variable that contains your Graph SubTitle*/
Put”                 text: ‘&Graphsubtitle’,”;
Put”                 x: -20″;
Put”             },”;
Put”             xAxis: {“;
/* Macro variable that contains Categories*/
Put”                 categories: &Categories “;
Put”             },”;
Put”             yAxis: {“;
Put”                 title: {“;
/* Macro variable that contains your Y Axis Title Text*/
Put”                     text: ‘&yAxistitletext'”;
Put”                 },”;
Put”                 plotLines: [{“;
Put”                     value: 0,”;
Put”                     width: 1,”;
Put”                     color: ‘#808080′”;
Put”                 }]”;
Put”             },”;
Put”             tooltip: {“;
Put”                 valueSuffix: ‘°C'”;
Put”             },”;
Put”             legend: {“;
Put”                 layout: ‘vertical’,”;
Put”                 align: ‘right’,”;
Put”                 verticalAlign: ‘middle’,”;
Put”                 borderWidth: 0″;
Put”             },”;
Put”             series: [{“;
Put”&series”; /* Macro variable that contains series*/
Put”             }]”;
Put”         });”;
Put”     });”;
Put” “;
Put” “;
Put”           “;
Put”     </head>”;
Put”     <body>”;
Put” “;
Put” “;
Put” “;
Put”310px; height: 400px; margin: 0 auto””>”;
Put” “;
Put”     </body>”;
Put” </html>”;
Put” “;
run;

* restore the quoteLenMax options. ;

OptionsquoteLenMax ;

Second Method: Proc STREAM and Macro facility

The STREAM procedure is a new experimental procedure available in SAS 9.3. It processes a SAS generated input stream, including macro specifications and logic and directs the generated text to any fileref.

PROC STREAM can be used with the generated macro variables as follow:

ProcStream_01

 Once you understand the example in this article, then it is quite easy to generate a higher quality interactive Charts and Graphs using JavaScript libraries  within SAS Stored Process, and believe me your customers will be impressed and maybe even surprised with the Charts and Graphs component.
ASSEM Redouan
SAS BI Consultant

assemredouan@hotmail.com


Leave a comment

How to find out Non-Printable characters in your SAS dataset

 

Introduction

Non printable characters in SAS dataset create potential problems in producing quality deliverables on a UNIX platform. These characters might be imported into dataset when the data is imported from applications such as Excel, Word document, XML or database. They could also occur if your database text is in Unicode and includes characters that cannot be displayed in the normal Windows Western European code page.

Following are some of the issues that might be caused by Non printable characters.

  • Incorrect results when filtering data.
  • Incomplete or incorrect displays of the data. The line / page alignment in the output generated is disrupted when some of these characters are present in the output.
  • Incorrect results being returned by the query when generating statistics or counts in the outputs.

The good news is they are easy to identify and then to filter out or to replace. Here we will see how to identify and generate reports regarding Non-Printable characters in SAS datasets.

Understanding Non printable characters

The ASCII character set has a printable and Non-Printable characters. To further understand them, we have to look into ASCII table.

We can broadly classify the characters into 3 groups:

  1. The first 32 characters in the ASCII-table (decimal value from 0 to 31) and the DEL char (decimal value 127) are unprintable control codes and are used to control peripherals such as printers.
  2. 94 standard printable characters (decimal value range from 33 to 126) which represent letters, digits, punctuation marks, and a few miscellaneous symbols.
  3. 128 special characters (Extended ASCII or ISO-8859-1. Decimal values range from 128 to 255). Decimal values from 128 to 159 in the Extended ASCII set are non-printing control characters.

The following table display the Non-Printable characters including the hex and octal codes.

Oct Hex Character NameTop of Form
0 0 ^@ (Cntl-@) NUL Null
1 1 ^A (Cntl-A) STX Start of Header
2 2 ^B SOT Start of Text
3 3 ^C ETX End of Text
4 4 ^D EOT End of Transmission
5 5 ^E ENQ Enquiry
6 6 ^F ACK Acknowledge
7 7 ^G BEL Bell
8 8 ^H BS BackSpace
9 9  ^I HT Horizontal Tabulation
10 0A ^J LF Line Feed
11 0B ^K VT Vertical Tabulation
12 0C ^L FF Form Feed
13 0D ^M CR Carriage Return
14 0E ^N SO Shift Out
15 0F ^O SI Shift In
16 10 ^P DLE Data Link Escape
17 11 ^Q DC1 Device Control 1 (XON)
18 12 ^R DC2 Device Control 2
19 13 ^S DC3 Device Control 3 (XOFF)
20 14 ^T DC4 Device Control 4
21 15 ^U NAK Negative acknowledge
22 16 ^V SYN Synchronous Idle
23 17 ^W ETB End of Transmission Block
24 18 ^X CAN Cancel
25 19 ^Y EM End of Medium
26 1A ^Z SUB Substitute
27 1B ^[ ESC Escape
28 1C ^- FS File Separator
29 1D ^] GS Group Separator
30 1E ^^ RS Record Separator
31 1F ^_ US Unit SeparatorBottom of Form

 

Some of the most common Non-Printable characters are carriage return, form feed, line feed, backspace, escape, horizontal tab and vertical tab.

For example, if you have carriage returns in the input .csv file, when you display the file then you probably will not see these carriage returns. But you can display them using cat command (or vi) and set an option -v like this:

cat -v myfile.csv

And normally you will see them at the end of the line as ^M (Cntl-M).

Finding out Non-Printable characters in SAS dataset

For this purpose I will share with you a macro that I have developed that identifies and generates a detailed report of all occurrences of Non-Printable characters in a given database.

Armed with this report you can easily decide what necessary action can be taken depending on one’s requirements. Here is the full code:

 

Libname MyLib ‘/sas/data/library/Sales/source’;

%LET MyLib= MyLib ;

%LET Tablename = MyTable;

%macro FindOutNonPrintableChar ;

/* Abort Macro execution if system errors */

%IF &syserr ne 0%THEN%DO;

%put System errors occured. ;

%goto exit;

%END;

/* Create table output */

data _output ;

length MyLib           $15

MyLibPath       $250

Tablename       $100

ColumName       $32

NonPrintableCode $4

NbrObsNonPrintableChar 8;

stop ;

run ;

/*Checks the existence in Metadata of the (physical) SAS data set otherwise end the macro */

%IF%sysfunc(exist(&MyLib..&&Tablename))= 0%THEN%DO;

%PUT NOTE: The following Table does not exist in Metadata: &MyLib..&&Tablename;

%goto exit;

%END;

/* Get character Column to check */

proc contents data=&MyLib..&&Tablename

out= ColumnsTocheck (keep=name type where =(type = 2)) noprint ;

run;

%LET ColumnsList = ;

Proc sql noprint;

Select name into :ColumnsList separated by ‘ ‘

from ColumnsTocheck ;

Quit;

%PUT NOTE: Char Columns to check regarding &MyLib..&&Tablename = &ColumnsList;

/* Check the existence of character Columns otherwise end the macro */

%IF &ColumnsList eq %THEN%DO;

%PUT NOTE: The following Table : &MyLib..&&Tablename contain only numeric columns.;

%goto exit;

%END;

/* Get Non-Printable Characters */

Data NonPrintableChar (keep = not_ascii column obs);

Set &MyLib..&&Tablename (keep = &ColumnsList) ;

length ascii_string $ 32;

retain ascii_string;

array colList &ColumnsList ;

if _n_ eq 1 then do;

do x = 0 to 31;

ascii_string=trim(left(ascii_string))||byte(x);

end;

end;

do over colList;

not_ascii=findc(collist,ascii_string);

Column = vname(colList);

Obs = _n_;

if not_ascii > 0 then output;

end;

run;

/* Count Non-Printable Characters found*/

Proc sql noprint;

Select count(*) into:NbrNonPrintableChar from NonPrintableChar ;

quit;

%IF &NbrNonPrintableChar eq 0%THEN%DO;

%PUT NOTE: No Non-Printable Characters found in the following Table : &MyLib..&&Tablename;

%goto exit;

%END;

%LET columnWithNonPrintChar = ;

Proc sql noprint;

Select Distinct column into :columnWithNonPrintChar separated by ‘ ‘

from NonPrintableChar;

Quit;

%PUT NOTE: Columns With Non-Printable Characters regarding &MyLib..&&Tablename = &columnWithNonPrintChar ;

Data DataToCheck ;

SET &MyLib..&&Tablename (keep = &columnWithNonPrintChar);

run;

/* Get Non-Printable Characters (0-31) */

%DO x = 0%to31;

Data _null_ ;

call symput (‘ascii_string’, “byte(&x)”);

run;

Data NonPrintableChar_&x (keep = not_ascii column obs );

Set DataToCheck ;

array colList &columnWithNonPrintChar ;

do over colList;

not_ascii=findc(collist,&ascii_string);

Column = vname(colList);

Obs = _n_;

if not_ascii > 0 then output;

end;

run;

Proc sql noprint;

Select count(*) into:NbrObs from NonPrintableChar_&x;

quit;

%PUT NOTE: NBR of Total (column) Records with the Non-Printable Characters –> &ascii_string regarding &MyLib..&&Tablename = &NbrObs ;

%IF &NbrObs > 0%THEN%DO ;

Proc sql;

Create table _output_&x as

Select

“&MyLib”       as MyLib         length = 15   label = ‘MyLib’ ,

“&MyLibPath”   as MyLibPath     length = 250 label = ‘Physical path’,

“&Tablename”   as Tablename       length = 100 label = ‘Physical Name’,

Column         as ColumName       length = 32   label = ‘Column Name’,

“&x”           as NonPrintableCode       length = 4   label = ‘Non-Printable character code 0-31’,

Count(obs)     as NbrObsNonPrintableChar length = 8   label = ‘Count of Non-Printable character in Column’

From NonPrintableChar_&x

Group by column ;

quit;

proc append base = _output data = _output_&x ;

run ;

%END ;

PROC Datasets library=work nolist ;

delete   _output_&x NonPrintableChar_&x;

run;

%END;

%exit:

%MEND FindOutNonPrintableChar ;

%FindOutNonPrintableChar;

 

The output table contains the followings:

 

Column Description
MyLib Libname
MyLibPath Libname path where the physical dataset reside
Tablename Dataset name
ColumName The column that contains Non-Printable char
NonPrintableCode The code of NonPrintable (see table above)
NbrObsNonPrintableChar Count of the Non-Printable Characters found in column

 

Here is an example of output:

MyLib Tablename ColumName NonPrintableCode NbrObsNonPrintableChar
MyLib MyTable MyColumn1 10 143
MyLib MyTable MyColumn1 13 143
MyLib MyTable MyColumn2 26 437
MyLib MyTable MyColumn3 26 3
MyLib MyTable MyColumn3 26 11

 

Hope this is helpful.

 

 

ASSEM Redouan

SAS BI Consultant

assemredouan@hotmail.com

Follow

Get every new post delivered to your Inbox.