Problem Solvers

1月 072021
 

This is the last of three posts on our hot-fix process, aimed at helping you better manage your SAS®9 environment through tips and best practices. The first two installments are linked here:

Having a good understanding of the hot-fix process can help you keep your SAS environment running smoothly. This last installment aims to help you get on a schedule with your hot-fix installations and provides an example spreadsheet (available for download on GitHub) to manage hot fixes.

Schedule hot fixes

As an administrator, sometimes applying outstanding hot fixes can be a daunting task. However, the longer you wait, the worse your situation becomes—with a potentially unstable environment and a growing backlog of hot fixes to apply. With a little careful planning, the task can become routine and everyone involved will be much happier. The next sections outline a strategy for getting on a quarterly schedule.

Run the SASHFADD tool

The first step of getting on a quarterly schedule to apply hot fixes is to run the SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool. For information about running this tool and analyzing the report it generates, see the first two installments in this blog series, The SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool and Understanding the SAS Hot Fix Analysis, Download and Deployment Tool Report.

Once you review the SASHFADD report, you will have a better understanding of what resources will be needed to apply the outstanding hot fixes. You also need to decide which philosophy of installing hot fixes you want to follow. For more information, see Which hot fixes should I apply?

Coordinate with IT

The second step is to coordinate the process with your IT department. Before you take the system offline to apply hot fixes, IT typically wants to do the following:

  • Perform a full system backup.
  • Check for scheduled jobs and make necessary adjustments.
  • Decide the best time to stop SAS services.
  • Evaluate how long the system will need to be offline.

After the first session of applying your hot-fix backlog, all these tasks can run on a regular (preferably at least quarterly) schedule that won't require as much analysis time from IT.

Communicate with end users

Before you implement the plan that you and the IT department devised, you need to communicate with your end users. Let them know ahead of time (maybe by a week) when the outage will occur, what they need to do to prepare for it, and how long it will take. It's a best practice to perform the update outside of regular business hours.

Reap the benefits

When you follow a quarterly schedule of applying hot fixes, there are many benefits:

  • The administrator is more experienced from installing hot fixes regularly, so the process goes more smoothly and takes less time.
  • The IT department has an established process in place for backing up the system and taking it offline for the maintenance.
  • End users know what to expect and are not surprised by the outage.
  • The system runs better and is protected from vulnerabilities due to the regular schedule of updates.
  • There is less downtime because there are fewer hot fixes to install with each update.

Manage hot fixes

Applying hot fixes can often be a complicated process with multiple steps before and after you install them. So, a key aspect of successfully applying hot fixes is ensuring that you follow all the steps that are included in the SASHFADD report. A great tool for managing this complexity is a spreadsheet!

Download one I created and customize it:

SANDY'S SPREADSHEET | DOWNLOAD IT NOW

This tool allows you to see and then check off (through highlighting, color coding, or notes) each of the steps to get the best results.

Administrators will have different approaches to their spreadsheets. Mine, linked above, is the result of much trial and error. Here are the items that I keep track of in my spreadsheet:

  • Hot-fix number
  • Hot-fix dependencies
  • Pre-installation steps
  • Post-installation steps
  • Special notes

Another benefit of the spreadsheet is that you can group steps together so that you can do them all at once. Here are some examples of when you can group steps to save time:

  • When performing the Rebuild Web Applications step, you can select the SAS® Marketing Automation, SAS® Marketing Optimization, and/or SAS® Deployment Manager web applications to be rebuilt in one iteration.
  • When performing the Deploy Web Applications step, you can select the SAS® Marketing Automation, SAS® Marketing Optimization, and/or SAS® Deployment Manager web applications to be rebuilt in one iteration.

Helpful resources

See the following links for the detailed and thorough documentation:

I hope that this blog series has been helpful to you! Have a terrific day!

READ PART ONE | The SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool READ PART TWO | Understanding the SAS Hot Fix Analysis, Download and Deployment Tool Report

How to schedule and manage your SAS hot fixes was published on SAS Users.

12月 082020
 

This is the second of three posts on our hot-fix process, aimed at helping you better manage your SAS®9 environment through tips and best practices. The first, an overview of the SASHFADD tool, is linked here and at the end of this post.

Having a good understanding of the hot-fix process can help you keep your SAS environment running smoothly. This second installment aims to help you understand hot-fix dependencies by giving you a sneak peek into the report generated by the SASHFADD tool.

SASHFADD tool report

Here's what the SASHFADD tool report looks like:

The citation key for items in the report is listed at the bottom of the page. This blog takes you through an example report for the SAS middle tier to show you how the citation key (shown below) works. For full details about the items in the citation key, see the SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool Usage Guide.

My report, SAS Middle Tier 9.4_M6 for Windows for x64, shown in the display below contains the following:

  • [1] for Issues Addressed
  • [A] for Alert
  • [C] for Container
  • [D] for Documentation
  • [S] for Security

What do the citations mean to you? Well, you need to click the citations to get a bigger picture of what this hot fix contains. Let's examine each citation in my example report in detail.

Citation key example

When you click the [1] for Issue(s) Addressed, it takes you to a page that lists all the SAS Notes that are being addressed in the fix:

If you scroll to the bottom of that section, it shows you whether there are other hot-fix dependencies or requirements that you need to meet before installing this hot fix. In this example, it shows that you must also install Hot Fix E3I012.

In the list of SAS Notes, any alert SAS Notes are labeled with ALERT. This report includes an [A], and you can see the alert SAS Note marked in the list. This citation is helpful if you want to update your system only for critical issues. If that is your preference, there is a SASHFADD script that will automatically download only the alert hot fixes and any corresponding dependency hot fixes.

The [C] in the report shows that it is a container hot fix. This citation is not clickable; it is there to indicate that you should refer to the documentation for a list of hot fixes in the container. When you click the [D], you see any relevant installation instructions. This page also lists the "member" hot fixes of the D8T013 "container" hot fix:

This page includes important notes along with installation instructions, and sometimes also includes pre-installation and/or post-installation instructions.

In this example, the post-installation instructions refer to a security update, which corresponds with the [S] from the report. Security updates might require additional steps. For example, some security updates require that you reapply them after you install the latest hot fixes. The security update will have its own instructions, so be sure to carefully read and follow all the steps.

Remember that the SASHFADD tool that generates this report can keep you from missing essential dependencies and instructions!

SASHFADD tool tips

Here are a couple of tips about using the SASHFADD tool.

Recommendation: If you ran the SASHFADD report a while ago, it is a good idea to run the SASHFADD report again before you apply your hot fixes so that you have the most up-to-date information. As you can see, when I ran the SASHFADD tool today, E3I012 is on the list as a dependency, not E3I011.

Reminder: If you run the SASHFADD tool manually, make sure that you get the updated SAS9X_HFADD_data.xml file before generating the updated report. Otherwise, you are running the same report you had before. This XML file is available on page 16 in the SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool Usage Guide:

SAS Note versus SASHFADD report for checking dependencies

Sometimes, people access hot fixes via a SAS Note rather than through a SASHFADD report. If you install the hot fix via a SAS Note such as SAS Note 66740, be sure to complete these steps:

  1. Click the Hot Fix tab and click the appropriate link for your release.
  2. Go to the bottom of the hot-fix page and check whether there are any dependent hot fixes.
  3. (This step applies to both methods for checking dependencies – via a SAS Note or via a SASHFADD report.) Before you install the dependent hot fixes, check whether they are already installed by looking at the View Registry report. You should also make sure that the version matches. In this example, you can see that the version matches but that Hot Fix E3I012 is not installed:

The SASHFADD report already listed the above dependent hot fix, so it would be part of my install if I used the SASHFADD report as my guide:

Tip: It is worth noting that when I click the link for E3I011, it shows that E31011 was replaced with E3I012:

 

The biggest takeaway message that I would like you to get from this blog is this: If you do not install the dependent hot fixes and/or follow the instructions such as the post-installation steps, you will encounter issues that will cause more downtime. This is something that I know we all want to avoid! If you run the SASHFADD tool, it automatically downloads all dependent hot fixes that you are eligible for, which eliminates the need for you to review the list of dependencies from any download page.

Helpful resources

See the following links for the detailed and thorough documentation:

Coming soon: third and final post in the series

The third blog in the series is about getting on a schedule with your hot-fix updates.

Thank you and have a wonderful day!

READ PART ONE | The SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool

Understanding the SAS Hot Fix Analysis, Download and Deployment Tool Report was published on SAS Users.

11月 252020
 

Helping customers is my passion. Having a good understanding of the hot-fix process can help you keep your SAS environment running smoothly. To help you better manage your environment, I am writing a series of blog posts with best practices and tips to successfully administer SAS hot fixes for SAS®9 environments. This first entry in the series focuses on the SAS Hot Fix Analysis, Download and Deployment Tool.

Importance of installing hot fixes

During my time helping SAS customers, I have seen many sites that did not apply hot fixes correctly or at all, which in turn caused them unnecessary downtime on their production environment(s). These issues could have been avoided if they had kept up-to-date with the hot fixes that were available. I've also seen many cases where the dependencies for hot fixes were not considered or the post-installation steps were not performed, again causing unnecessary downtime. Hopefully, some of the tips in this blog series will help SAS administrators realize just how important hot fixes are.

Benefits of the SASHFADD Tool

The focus of this post is the SAS Hot Fix Analysis, Download and Deployment Tool, commonly known as SASHFADD. This is the tool that SAS recommends for you to always use in SAS® 9.3 and SAS® 9.4 environments when applying hot fixes. Why? There are many reasons!

  • It creates a report that shows the products that are installed in your environment that have outstanding hot fixes. The report goes into detail about dependencies that you need to address before installing hot fixes. My next post provides a detailed analysis of an example report.
  • After you run the SASHFADD.exe file, it creates a DOWNLOAD<name> directory with some additional scripts that allow you to automatically download all your hot fixes into the DEPLOY<name> directory, so that you do not have to manually download each file. If you want to download only the alert hot fixes, there is a script available in the DOWNLOAD<name> directory to do that as well.
  • After you install SASFHADD, you need to run it periodically to check for hot fixes. One of the posts in this series provides more information about scheduling SASHFADD runs.
  • Overall, it provides a great picture of what is needed to keep your environment running in top shape.

Note: There are certain items that SASHFADD does not support. For details, see SAS Note 527, "SAS® hot fixes and patches that are not supported by the SAS Hot Fix Analysis, Download and Deployment Tool."

The SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool Usage Guide clearly describes the steps to install and use SASHFADD. If you haven't already installed this tool, you should do it right away! Always remember that if you run into an issue, this guide has a wonderful troubleshooting section.

Workaround if you are not the server administrator

This section primarily applies to consultants who are working onsite with the SAS administrator. If you are in a situation in which you do not have administrator access to the server or there are proxy issues, you can run SASHFADD on a different system. Doing this allows you to create the report on another system that you can use to plan your hot-fix implementation.

Here are the steps that you should follow on a Microsoft Windows system.

On the system where you plan to run the SASHFADD tool:

1. Download and execute the SASHFADD tool following the instructions in the Usage Guide.

2. Go to page 16 of the Usage Guide and click the appropriate HFADD_data.xml file to get the most recent list of hot fixes. Once it opens the file, save it in the SASHFADD

On the server that you want to apply the hot fixes to:

3. Go to the deploymntreg folder in SASHome.

4. Double-click the sas.tools.viewregistry.jar file. This action creates a create the Deployment Registry and accompanying txt file in the deploymntreg folder:

5. Copy the DeploymentRegistgry.txt file.

On the system where you plan to run the SASHFADD tool:

6. Paste the DeploymentRegistgry.txt file in the SASHFADD

7. In the SASHFADD folder, run the SASHFADD.exe file and provide a name for the tier that you are running it on. In the display below, the file is being run on the metadata tier:

Note that you must run the file on each tier. This action creates the report in a folder whose name corresponds to the name of the tier that you ran the EXE file on. You can then share this report (or multiple reports) with the SAS administrator who will be installing the hot fixes:

Helpful resources

See the following links for the detailed and thorough documentation:

Coming soon

The second post in the series is to help you understand dependencies covered in the SASHFADD report.

Thank you and have an awesome day!

 

The SAS Hot Fix Analysis, Download and Deployment (SASHFADD) Tool was published on SAS Users.

10月 202020
 

When you use SAS software, you might occasionally encounter an issue with SASUSER. This post helps you debug some of the more common issues:

  • a warning message indicates that SASUSER.TEMPLAT is not an item store or that you cannot write to SASUSER.TEMPLAT
  • a note in the log indicates that SAS cannot open the SASUSER.PROFILE catalog
  • a note in the log indicates that SAS cannot open the SASUSER.REGSTRY item store
  • various errors and abnormal endings occur when you use the SAS® Output Delivery System or create graphics output
  • access to SASUSER is read-only

Issue 1: SAS® writes a message to the log indicating that SASUSER.TEMPLAT is not an item store or that you cannot write to SASUSER.TEMPLAT

By default, SAS tries to store custom templates and styles that PROC TEMPLATE creates in SASUSER. In some SAS environments with multiple users on a server, your SASUSER location might be read-only (set with the RSASUSER option). If you do not need the template or style to persist between sessions, you can set the template path to include the WORK library first:

ods path(prepend) work.template(update);

If you are working with a local SAS session, this issue can occur when a corrupt or old copy of the templat.sas7bitm file exists in your SASUSER directory. To resolve the issue

  1. Determine the location of your SASUSER directory by submitting the following code to SAS:
proc options option=sasuser;
run;
  1. View the new information that is written to the log and make a note of the directory to which SASUSER points.
  2. Stop all running SAS sessions.
  3. From your operating system, open your SASUSER directory and rename templat.sas7bitm to templat.old.
  4. Restart SAS.

Issue 2: SAS® writes a note or warning to the log indicating that SAS cannot open SASUSER.PROFILE

If you see a note or warning in the log indicating that SAS cannot open the SASUSER.PROFILE catalog, you should ensure first that you have only a single SAS session running.  If you have multiple SAS sessions running concurrently only the first SAS session has Update access to SASUSER.

If only one SAS session is active and you still receive a note or warning that SAS cannot open SASUSER.PROFILE:

  1. Determine the location of your SASUSER directory by submitting the following code to SAS:
proc options option=sasuser;
run;
  1. Stop any running SAS sessions.
  2. Rename the following files in your SASUSER directory:

In Microsoft Windows operating environments, rename the files as follows:

  • profile.sas7bcat to profile.old
  • profbak.sas7bat to profbak.old
  • profile2sas7bcat to profile2.old

In UNIX operating environments, rename the files as follows:

  • profile.sas7bcat to profile.old
  • profile.sas7bcat to profbak.old

Issue 3: SAS® writes a note or warning to the log indicating that SAS cannot open the SASUSER.REGSTRY item store

If you see a note or warning in the log indicating that SAS cannot open SASUSER.REGSTRY, ensure first that you have only a single SAS session running. If you have multiple SAS sessions running concurrently only the first SAS session has Update access to SASUSER.

If only one SAS session is active and you still receive a note or warning that SAS cannot open SASUSER.REGSTRY:

  1. Determine the location of your SASUSER directory by submitting the following code to SAS:
proc options option=sasuser;
run;
  1. Stop any running SAS sessions.
  2. From your operating environment, open your SASUSER directory and rename regstry.sas7bitm to regstry.old.
  3. Restart SAS.

Issue 4: Various abnormal endings and errors occur when you use SAS Output Delivery System (ODS) or when you create graphics output

If one or more files or catalogs in SASUSER are corrupted, various abnormal endings and errors can occur when you use ODS or when you create graphics output.

If you suspect that this is the case, determine the location of your SASUSER directory by submitting the following code to SAS:

proc options option=sasuser;
run;
  1. View the new information that is written to the log and make a note of the directory to which SASUSER points.
  2. Stop all running SAS sessions.
  3. From your operating environment, open the SASUSER directory and rename the following files (if they exist) as shown:
  • profile2.sas7bcat to profile2.old
  • regstry.sas7bitm to regstry.old
  • templat.sas7bitm to templat.old
  1. Restart SAS.

Issue 5: Access to the SASUSER directory is Read-Only

If you follow the debugging steps for any of the issues outlined above and find that you still have Read access to SASUSER, the problem might be with your SAS installation. Specifically, your installation

proc options option=rsasuser;
run;

In a multiuser SAS environment or SAS Grid Computing environment, RSASUSER might be set by policy. In that case, you must adjust your programs/process to not rely on SASUSER for personal content. If working with a local or private SAS environment, you can change the option to NORSASUSER in your SAS configuration file.

Summary

As you can see from this post, a variety of reasons can cause issues with the SASUSER directory. These issues can occur when one or more catalogs or item stores in your SASUSER directory become corrupted or are created with an earlier installation of SAS. However, if you rename the catalogs or item stores with a file extension that SAS does not recognize, SAS creates new, uncorrupted copies of these files when you restart SAS.

Debugging SASUSER issues when you use SAS® software was published on SAS Users.

9月 162020
 

Many examples for performing analysis of covariance (ANCOVA) are available in statistical textbooks, conference papers, and blogs. One of the primary interests for researchers and statisticians is to obtain a graphical representation for the fitted ANCOVA model. This post, co-authored with my SAS Technical Support colleague Kathleen Kiernan, demonstrates how to obtain the statistical analysis for a quadratic ANCOVA model by using the GLM procedure and graphical representation by using the SGPLOT procedure.

For the purposes of this blog, the tests for equality of intercepts and slopes have been omitted. For more detailed information about performing analysis of covariance using SAS, see SAS Note 24177, Comparing parameters (slopes) from a model fit to two or more groups.

Fitting the model

Keep in mind that the statistical methods for ANCOVA are similar whether you have a one-way ANCOVA model or a repeated-measures ANCOVA model. The example data below consists of three treatments, the covariate X and the response value Y. Predicted observations were generated based upon augmenting observations to the training data set that is used to fit the final model.

data ts132;
input trt x y @@;
datalines;
1 2.2 3.7 1 3.5 4.7 1 4.1 5.3 1 4.1 5.5 1 7.4 5.7 1 3.0 3.8
1 0.5 1.0 1 2.4 3.3 1 8.4 5.3 1 8.5 5.3 1 8.0 6.1 1 6.3 5.8
2 5.8 7.2 2 9.9 2.5 2 1.2 4.3 2 6.0 6.9 2 5.5 8.0 2 9.7 3.7
2 8.6 5.2 2 5.0 8.0 2 2.6 6.9 2 8.4 4.3 2 3.0 6.8 2 5.2 7.6
3 8.8 5.7 3 9.0 6.1 3 8.8 6.4 3 4.1 9.6 3 2.4 9.4 3 4.1 8.2
3 4.4 10.2 3 8.9 6.0 3 5.3 9.4 3 9.5 5.0 3 1.1 7.5 3 0.1 5.3
;
run;
data new;
do trt=1 to 3;
x=0; y=.; 
output;
x=10; 
output;
end;
run;
data combo;
set ts132 new;
run;

The following statements fit the final model with common slope in the direction of x and unequal quadratic slope in direction of X*X.

proc glm data=combo;
class trt;
model y=trt x x*x*trt/solution noint;
output out=anew p=predy;
ods output parameterestimates=pe;
quit;

The Type III SS displays significance tests for the effects in the model.

The F statistic that corresponds to the TRT effect provides test intercepts (α123=0). The F statistic  that corresponds to X provides test β1=0. The X*X*TRT effect provides test β123=0. The significance levels are very small, indicating that there is sufficient evidence to reject the null hypothesis.

Displayed next: parameter estimates

You can write the parameter estimates to a data set by using the PARAMETERESTIMATES output object in an ODS OUTPUT statement in order to display the model equations on a graph.

The default ODS graphics from PROC GLM provides the following graphic representation of the model:

However, researchers, managers and statisticians often want to modify the graph to include the model equation, to change thickness of the lines, symbols, titles and so on.

You can use PROC SGPLOT to plot the ANCOVA model and regression equations by using the OUTPUT OUT=ANEW data set from PROC GLM, as shown in this example:

proc sgplot data=anew;
scatter y=y x=x /group=trt;
pbspline y=predy x=x / group=trt smooth=0.5 nomarkers;
run;

This code produces the following graph:

You can see that the respective graphs from PROC GLM and PROC SGPLOT look similar.

Customizing your graphs

It is easier to use PROC SGPLOT to enhance graphs rather than to do the same by editing the ODS Graphics Template associated with the GLM procedure.

To color coordinate the respective treatments with the regression model equations and display them in the graph, you can use SG annotation. The TEXT function is used to place the text at specific locations within the graph.

The POLYGON and POLYCONT functions are used to draw a border around the equations. Note that you might need to adjust the values for X1 and Y1 in the annotate data set for your specific output.

data eqn;
set pe(keep=parameter estimate) end=last;
retain trt1 trt2 trt3 x x_x1 x_x2 x_x3;
length function $9 textcolor $30;
x1space='datapercent'; y1space='wallpercent';
function='text';
x1=0;
anchor='left';
width=120;
select (compbl(parameter));
when ('trt 1') trt1=estimate;
when ('trt 2') trt2=estimate;
when ('trt 3') trt3=estimate;
when ('x') do;
if estimate <0 then x=cats(estimate,'x');
else x=cats(' + ',estimate,'x');
end;
when ('x*x*trt 1') do;
if estimate < 0 then x_x1=cats(estimate,'x*x*trt1');
else x_x1=cats(' + ',estimate,'x*x*trt1');
end;
when ('x*x*trt 2') do;
if estimate < 0 then x_x2=cats(estimate,'x*x*trt2');
else x_x2=cats(' + ',estimate,'x*x*trt2');
end;
when ('x*x*trt 3') do;
if estimate < 0 then x_x3=cats(estimate,'x*x*trt3');
else x_x3=cats(' + ',estimate,'x*x*trt3');
end;
otherwise;
end;
if last then do;
label=cats("Y=",trt1,x,x_x1);
y1=3;
textcolor="graphdata1:contrastcolor";
output;
label=cats("Y=",trt2,x,x_x2);
y1=10;
textcolor="graphdata2:contrastcolor";
output;
label=cats("Y=",trt3,x,x_x3);
y1=17;
textcolor="graphdata3:contrastcolor";
output;
function='polygon';
x1=0; y1=0;
linecolor='grayaa';
linethickness=1;
output;
function='polycont';
y1=21; output;
x1=60; output;
y1=0; output;
end;
run;
proc sgplot data=anew sganno=eqn;
scatter y=y x=x /group=trt;
pbspline y=predy x=x / group=trt smooth=0.5 nomarkers;
 yaxis offsetmin=0.3;
run;

This code results in the following graph with model equations:

You might also want to add a title; change the X or Y-axis label; or change the color, thickness and pattern for the lines as well as the symbol for the data points, as follows:

  • Use a TITLE statement to add a title to the graph.
  • Use the LABEL= option in the XAXIS and YAXIS statements in PROC SGLPLOT to specify a label for the axis, with the LABELATTRS= option specifying attributes for those labels.
  • Use the STYLEATTRS statement in PROC SGPLOT to specify the attributes for the lines and marker symbols.

The following PROC SGPLOT code produces a graph with the customizations described above:

ods graphics / attrpriority=none;
title 'Fitted Plot for Quadratic Covariate with Unequal Slopes'; 
proc sgplot data=anew sganno=eqn;
styleattrs datacontrastcolors=(navy purple darkgreen)
datalinepatterns=(1 2 44)
datasymbols=(squarefilled circlefilled
diamondfilled);
scatter y=y x=x /group=trt;
pbspline y=predy x=x / group=trt smooth=0.5 nomarkers;
yaxis offsetmin=0.3 label='Y value' 
labelattrs=(size=10pt color=black weight=bold);
xaxis label='X value' 
labelattrs=(size=10pt color=black weight=bold);
run;

Keep in mind that you also need to adjust the color for the annotated text by changing the value for the TEXTCOLOR= variable (in the DATA step) from textcolor="graphdata1:contrastcolor"  to reference the new colors, as shown in this example: textcolor="navy";

The resulting graph, including the modifications, is displayed below.

Conclusion

Using the output data set from your statistical procedure enables you to take advantage of the functionality of PROC SGPLOT to enhance your output.  This post describes just some of the customizations you can make to your output.

References

Milliken, George. A., and Dallas E. Johnson. 2002. Analysis of Messy Data, Volume III: Analysis of Covariance. London: Chapman and Hall/CRC.

SAS Institute Inc. 2006. SAS Note 24529, "Modifying axes, colors, labels, or other elements of statistical graphs produced using ODS Graphics." Available at support.sas.com/kb/24/529.html.

Using SAS® to analyze and visualize a quadratic ANCOVA model was published on SAS Users.

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

6月 232020
 

When you execute code on the SAS® Cloud Analytic Services (CAS) server, one way you can improve performance is by partitioning a table. A partitioned table enables the data to load faster when that data needs to be grouped by the common values of a variable.

This post explains what it means to partition a table and describes the advantages of a partitioned table. I'll illustrate these concepts with example code that shows improved processing time when you use a partitioned table.

SAS BY-group requires SORT

BY-group processing enables you to group your data by unique variable values. This processing is used, for example, in tasks like merging data sets by a common variable and producing reports that contain data that is grouped by the value of a classification variable. You also use BY-group processing when you execute code in CAS. A key difference in creating BY-groups in SAS versus in CAS is that SAS requires a SORT procedure to sort the data by the specified BY variable in order to create the BY groups.

BY-group sorting implicit in CAS

This step is not required in CAS. When you perform BY-group processing on a CAS table, an implicit sorting action takes place, and each BY-group is distributed to the available threads. This implicit sort process takes place each time that the table is accessed and the BY-groups are requested.

Partitioning a CAS table permanently stores the table such that the values of the BY variable are grouped. Using a partitioned CAS table enables you to skip the implicit sort process each time the table is used, which can greatly improve performance.

Partition CAS action example

You create a partitioned CAS table by using the partition CAS action. The following example shows how to partition the CARS table (created from the SASHELP.CARS data set) by the MAKE variable.

caslib _all_ assign;    ①
data casuser.cars;      
set sashelp.cars;  ②
run;
proc cas;
table.partition /     ③                                              
casout={caslib="casuser", name="cars2"} ④                            
table={caslib="casuser", name="cars", groupby={name="make"}}; ⑤
quit;

In this code:

①  The CASLIB statement creates SAS librefs that point to all existing caslibs. CASUSER, which is used in the subsequent DATA step, is one of the librefs that are created by this statement.

②  The DATA step creates the CARS CAS table in the CASUSER caslib.

③  The partition action in the CAS procedure is part of the TABLE action set.

④  The casout= parameter contains the caslib= parameter, which points to the caslib where the partitioned CAS table named CARS2 will be stored.

⑤ The table= parameter contains the name= parameter, which lists the name of the table that is being partitioned. It also contains the CASLIB= option, which points to the caslib in which the table is stored. The groupby= parameter contains the name= option, which names the variable by which to partition the table.

You can confirm that the table has been partitioned by submitting the following CAS procedure with the tabledetails action.

proc cas;
table.tabledetails result=r / level='partition' ①
name='cars2'
caslib='casuser';
run;
describe r; ②
quit;

In this code:
① The LEVEL= parameter specifies the aggregation level of the TABLEDETAILS output.
② The DESCRIBE statement writes the output of the TABLEDETAILS action to the log.

The following output is displayed in the resulting log. The KEY column shows the variable the table has been partitioned by.

As mentioned earlier, the purpose of partitioning a table is to improve performance. The following example uses two CAS tables that consist of an ID variable with 10 possible values and 10 character variables. Each table contains 5,000,000 rows. This example illustrates how much performance improvement you can gain by partitioning the tables. In this case, the ID variable merges two tables.

First, you create the tables by submitting the following DATA steps:

data casuser.one;
array vars(10) $8 x1-x10;
do j=1 to 5000000;
id=put(rand('integer',1,10),8.);
do i=1 to 10;
vars(i)=byte(rand('integer',65,90));
end;
output;
end;
drop i j;
run;
 
data casuser.two;
array vars(10) $8 x1-x10;
do j=1 to 5000000;
id=put(rand('integer',1,10),8.);
do i=1 to 10;
vars(i)=byte(rand('integer',65,90));
end;
output;
end;
drop i j;
run;

The DATA steps above show how to merge non-partitioned tables. In the log output shown below, you can see that the total, real time (highlighted) took almost 45 seconds to run.

Partitioned tables example

The next example runs the same DATA step code, but it uses partitioned tables. The first step is to partition the tables, as shown below:

proc cas;
table.partition /                                                   
casout={caslib="casuser", name="onepart"}                             
table={caslib="casuser", name="one", groupby={name="id"}};   
run; 
 
table.partition /                                                   
casout={caslib="casuser", name="twopart"}                             
table={caslib="casuser", name="two", groupby={name="id"}};   
quit;

To merge the two tables and to product the log, submit the following code. (The real time is highlighted in the log.)

data casuser.nopart;
merge casuser.onepart casuser.twopart;
by id;
run;

The output for this code is show below:

This time, the DATA Step took only 25.43 seconds to execute. That is a 43% improvement in execution time!

Partition for improved performance

If your analysis requires you to use the same table multiple times to perform BY-group processing, then I strongly recommend that you partition the table. As the last example shows, partitioning your table can greatly improve performance!

Partition your CAS tables to greatly improve performance 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.