All SAS^® data sets and variables have standard attributes. These include items such as creation date, engine, compression and sort information for data sets, and format and length information for variables. However, for the first time in SAS 9.4, the developer can add their own customized attributes to both data sets and variables. This paper shows how these extended attributes can be created, modified, and maintained. It suggests the sort of items that might be candidates for use as extended attributes and explains in what circumstances they can be used. It also provides a worked example of how they can be used to inform and aid the SAS programmer in creating SAS applications.

Looking for a handy technique to have in your toolkit? Consider SAS^® Views^®, especially if you work with large data sets. After a brief introduction to SAS Views, I'll show you several cool ways to use them that will streamline your code and save workspace.

Delimited text files are often plagued by appended and/or truncated records. Writing customized SAS^® code to import such a text file and break out into fields can be challenging. If only there was a way to fix the file before importing it. Enter the file_fixing_tool, a SAS^® Enterprise Guide^® project that uses the SAS PRX functions to import, fix, and export a delimited text file. This fixed file can then be easily imported and broken out into fields.

Read the paper (PDF). | Download the data file (ZIP).

Whenever you travel, whether it's to a new destination or to your favorite vacation spot, it's nice to have a guide to assist you with planning and setting expectations. The ODS LAYOUT statement became production in SAS^® 9.4. For those intrepid programmers who used ODS LAYOUT in an earlier release of SAS^®, this paper contains tons of information about changes you need to know about. Programmers new to SAS 9.4 (or new to ODS LAYOUT) need to understand the basics. This paper reviews some common issues customers have reported to SAS Technical Support when migrating to the LAYOUT destination in SAS 9.4 and explores the basics for those who are making their first foray into the adventure that is ODS LAYOUT. This paper discusses some tips and tricks to ensure that your trip through the ODS LAYOUT statement will be a fun and rewarding one.

The merge is one of the SAS^® programmer's most commonly used tools. However, it can be fraught with pitfalls to the unwary user. In this paper, we look under the hood of the DATA step and examine how the program data vector works. We see what's really happening when data sets are merged and how to avoid subtle problems.

You know that you want to control the process flow of your program. When your program is executed multiple times, with slight variations, you will need to control the changes from iteration to iteration, the timing of the execution, and the maintenance of output and logs. Unfortunately, in order to achieve the control that you know that you need to have, you will need to make frequent, possibly time-consuming and potentially error-prone, manual corrections and edits to your program. Fortunately, the control you seek is available and it does not require the use of time-intensive manual techniques. List processing techniques are available that give you control and peace of mind and enable you to be a successful control freak. These techniques are not new, but there is often hesitancy on the part of some programmers to take full advantage of them. This paper reviews these techniques and demonstrates them through a series of examples.

This presentation is an open-ended discussion about techniques for transferring data and analytical results from SAS^® to Microsoft Excel. There are some introductory comments, but this presentation does not have any set content. Instead, the topics discussed are dictated by attendee questions. Come prepared to ask and get answers to your questions. To submit your questions or suggestions for discussion in advance, go to http://support.sas.com/surveys/askvince.html.

We have to pull data from several data files in creating our working databases. The simplest use of SAS^® hash objects greatly reduces the time required to draw data from many sources when compared to the use of multiple proc sorts and merges.

Read the paper (PDF). | Download the data file (ZIP).

You've worked for weeks or even months to produce an analysis suite for a project. Then, at the last moment, someone wants a subgroup analysis, and they inform you that they need it yesterday. This should be easy to do, right? So often, the programs that we write fall apart when we use them on subsets of the original data. This paper takes a look at some of the best practice techniques that can be built into a program at the beginning, so that users can subset on the fly without losing categories or creating errors in statistical tests. We review techniques for creating tables and corresponding titles with BY-group processing so that minimal code needs to be modified when more groups are created. And we provide a link to sample code and sample data that can be used to get started with this process.

SAS^® provides a wealth of resources for creating useful, attractive metadata tables, including PROC CONTENTS listing output (to ODS destinations), the PROC CONTENTS OUT= SAS data set, and PROC CONTENTS ODS Output Objects. This paper and presentation explores some less well-known resources to create metadata such as %SYSFUNC, PROC DATASETS, and Dictionary Tables. All these options in conjunction with the use of the ExcelXP tagset (and, new in the second maintenance release for SAS^® 9.4, the Excel tagset) enable the creation of multi-tab metadata workbooks at the click of a mouse.

This presentation focuses on building a graph template in an easy-to-follow, step-by-step manner. The presentation begins with using Graph Template Language to re-create a simple series plot, and then moves on to include a secondary y-axis as well as multiple overlaid block plots to tell a more complex and complete story than would be possible using only the SGPLOT procedure.

The DATA step enables you to read, write, and manipulate many types of data. As data evolves to a more free-form state, the ability of SAS^® to handle character data becomes increasingly important. This presentation, expanded and enhanced from an earlier version, addresses character data from multiple vantage points. For example, what is the default length of a character string, and why does it appear to change under different circumstances? Special emphasis is given to the myriad functions that can facilitate the processing and manipulation of character data. This paper is targeted at a beginning to intermediate audience.

With all the talk of 'big data' and 'visual analytics' we sometimes forget how important it is, and often how hard it is, to get external data into SAS^®. In this paper, we review some common data sources such as delimited sources (for example, CSV), as well as structured flat files, and the programming steps needed to successfully load these files into SAS. In addition to examining the INFILE and INPUT statements, we look at some methods for dealing with bad data. This paper assumes only basic SAS skills, although the topic can be of interest to anyone who needs to read external files.

There is a plethora of uses of the colon (:) in SAS^® programming. The colon is used as a data or variable name wild-card, a macro variable creator, an operator modifier, and so forth. The colon helps you write clear, concise, and compact code. The main objective of this paper is to encourage the effective use of the colon in writing crisp code. This paper presents real-time applications of the colon in day-to-day programming. In addition, this paper presents cases where the colon limits programmers' wishes.

In previous papers I have described how many standard SAS/GRAPH^® plots can be converted easily to ODS Graphics by using simple PROC SGPLOT or SGPANEL code. SAS/GRAPH Annotate code would appear, at first sight, to be much more difficult to convert to ODS Graphics, but by using its layering features, many Annotate plots can be replicated in a more flexible and repeatable way. This paper explains how to convert many of your Annotate plots, so they can be reproduced using Base SAS^®.

This presentation explains how to use Base SAS^®9 software to create multi-sheet Excel workbooks. You learn step-by-step techniques for quickly and easily creating attractive multi-sheet Excel workbooks that contain your SAS^® output using the ExcelXP Output Delivery System (ODS) tagset. The techniques can be used regardless of the platform on which SAS software is installed. You can even use them on a mainframe! Creating and delivering your workbooks on-demand and in real time using SAS server technology is discussed. Although the title is similar to previous presentations by this author, this presentation contains new and revised material not previously presented.

Read the paper (PDF). | Download the data file (ZIP).

Hysteresis loops occur because of a time lag between input and output. In the pharmaceutical industry, hysteresis plots are tools to visualize the time lag between drug concentration and drug effects. Before SAS^® 9.2, SAS annotations were used to generate such plots. One of the criticisms was that SAS programmers had to write complex macros to automate the process; code management and validation tasks were not easy. With SAS 9.2, SAS programmers are able to generate such plots with ease. This paper demonstrates the generation of such plots with both Base SAS and SAS/GRAPH^® software.

The Centers for Disease Control and Prevention (CDC) went through a large migration from a mainframe to a Windows platform. This e-poster will highlight the Data Automated Transfer Utility (DATU) that was developed to migrate historic files between the two file systems using SAS^® macros and SAS/CONNECT^®. We will demonstrate how this program identifies the type of file, transfers the file appropriately, verifies the successful transfer, and provides the details in a Microsoft Excel report. SAS/CONNECT code, special system options, and mainframe code will be shown. In 2009, the CDC made the decision to retire a mainframe that was used for years of primarily SAS work. The replacement platform is a SAS grid system, based on Windows, which is referred to as the Consolidated Statistical Platform (CSP). The change from mainframe to Windows required the migration of over a hundred thousand files totaling approximately 20 terabytes. To minimize countless man hours and human error, an automated solution was developed. DATU was developed for users to migrate their files from the mainframe to the new Windows CSP or other Windows destinations. Approximately 95% of the files on the CDC mainframe were one of three file types: SAS data sets, sequential text files, and partitioned data sets (PDS) libraries. DATU dynamically determines the file type and uses the appropriate method to transfer the file to the assigned Windows destination. Variations of files are detected and handled appropriately. File variations include multiple SAS versions of SAS data sets and sequential files that contain binary values such as packed decimal fields. To mitigate the loss of numeric precision during the migration, SAS numeric variables are identified and promoted to account for architectural differences between mainframe and Windows platforms. To aid users in verifying the accuracy of the file transfer, the program compares file information of the source and destination files. When a SAS file is d ownloaded, PROC CONTENTS is run on both files, and the PROC CONTENTS output is compared. For sequential text files, a checksum is generated for both files and the checksum file is compared. A PDS file transfer creates a list of the members in the PDS and destination Windows folder, and the file lists are compared. The development of this program and the file migration was a daunting task. This paper will share some of our lessons learned along the way and the method of our implementation.

The DATA Step has served SAS^® programmers well over the years, and although it is handy, the new, exciting, and powerful DS2 is a significant alternative to the DATA Step by introducing an object-oriented programming environment. It enables users to effectively manipulate complex data and efficiently manage the programming through additional data types, programming structure elements, user-defined methods, and shareable packages, as well as threaded execution. This tutorial is developed based on our experiences with getting started with DS2 and learning to use it to access, manage, and share data in a scalable and standards-based way. It facilitates SAS users of all levels to easily get started with DS2 and understand its basic functionality by practicing the features of DS2.

Soon after the advent of the SAS^® hash object in SAS^® 9.0, its early adopters realized that the potential functionality of the new structure is much broader than basic 0(1)-time lookup and file matching. Specifically, they went on to invent methods of data aggregation based on the ability of the hash object to quickly store and update key summary information. They also demonstrated that the DATA step aggregation using the hash object offered significantly lower run time and memory utilization compared to the SUMMARY/MEANS or SQL procedures, coupled with the possibility of eliminating the need to write the aggregation results to interim data files and the programming flexibility that allowed them to combine sophisticated data manipulation and adjustments of the aggregates within a single step. Such developments within the SAS user community did not go unnoticed by SAS R&D, and for SAS^® 9.2 the hash object had been enriched with tag parameters and methods specifically designed to handle aggregation without the need to write the summarized data to the PDV host variable and update the hash table with new key summaries, thus further improving run-time performance. As more SAS programmers applied these methods in their real-world practice, they developed aggregation techniques fit to various programmatic scenarios and ideas for handling the hash object memory limitations in situations calling for truly enormous hash tables. This paper presents a review of the DATA step aggregation methods and techniques using the hash object. The presentation is intended for all situations in which the final SAS code is either a straight Base SAS DATA step or a DATA step generated by any other SAS product.

As SAS^® programmers and statisticians, we rarely write programs that are run only once and then set aside. Instead, we are often asked to develop programs very early in a project, on immature data, following specifications that may be little more than a guess as to what the data is supposed to look like. These programs will then be run repeatedly on periodically updated data through the duration of the project. This paper offers strategies for not only making those programs more flexible, so they can handle some of the more commonly encountered variations in that data, but also for setting traps to identify unexpected data points that require further investigation. We will also touch upon some good programming practices that can benefit both the original programmer and others who might have to touch the code. In this paper, we will provide explicit examples of defensive coding that will aid in kicking the tires, pumping the breaks, checking your blind spots, and merging ahead for quality programming from the beginning.

Intervals have been a feature of Base SAS^® for a long time, enabling SAS users to work with commonly (and not-so-commonly) defined periods of time such as years, months, and quarters. With the release of SAS^®9, there are more options and capabilities for intervals and their functions. This paper first discusses the basics of intervals in detail, and then discusses several of the enhancements to the interval feature, such as the ability to select how the INTCK function defines interval boundaries and the ability to create your own custom intervals beyond multipliers and shift operators.

This paper puts forward an approach on how to create duplicate records from one Adverse Event (AE) datum based on study treatments. In order to fulfill this task, one flag was created to check if we need to produce duplicate records depending on an existing AE in different treatment periods. If yes, then we create these duplicate records and derive AE dates for these duplicate records based on treatment periods or discontinued dates.

Being able to split SAS^® processing over multiple SAS processers on a single machine or over multiple machines running SAS, as in the case of SAS^® Grid Manager, enables you to get more done in less time. This paper looks at the methods of using SAS/CONNECT^® to process SAS code in parallel, including the SAS statements, macros, and PROCs available to make this processing easier for the SAS programmer. SAS products that automatically generate parallel code are also highlighted.

Read the paper (PDF). | Download the data file (ZIP).

Today, employers and universities alike must choose the most talented individuals from a large pool. However, it is difficult to determine whether a student's A+ in English means that he or she can write as proficiently as another student who writes as a hobby. As a result, there are now dozens of ways to compare individuals to one or another spectrum. For example, the ACT and SAT enable universities to view a student's performance on a test given to all applicants in order to help determine whether they will be successful. High schools use students' GPAs in order to compare them to one another for academic opportunities. The WorkKeys Exam enables employers to rate prospective employees on their abilities to perform day-to-day business activities. Rarely do standardized tests and in-class performance get compared to each other. We used SAS^® to analyze the GPAs and WorkKeys Exam results of 285 seniors who attend the Phillip O Berry Academy. The purpose was to compare class standing to what a student can prove he knows in a standardized environment. Emphasis is on the use of PROC SQL in SAS^® 9.3 rather than DATA step processing.

Read the paper (PDF). | Download the data file (ZIP).

This paper explains best practices for using temporary files in SAS^® programs. These practices include using the TEMP access method, writing to the WORK directory, and ensuring that you leave no litter files behind. An additional special temporary file technique is described for mainframe users.

Programming SAS^® has just been made easier, now that SAS 9.4 has incorporated the Lua programming language into the heart of the SAS System. With its elegant syntax, modern design, and support for data structures, Lua offers you a fresh way to write SAS programs, getting you past many of the limitations of the SAS macro language. This paper shows how you can get started using Lua to drive SAS, via a quick introduction to Lua and a tour through some of the features of the Lua and SAS combination that make SAS programming easier. SAS macro programming is also compared with Lua, so that you can decide where you might benefit most by using either language.

Dynamic, interactive visual displays known as dashboards are most effective when they show essential graphs, tables, statistics, and other information where data is the star. The first rule for creating an effective dashboard is to keep it simple. Striking a balance between content and style, a dashboard should be devoid of excessive clutter so as not to distract from and obscure the information displayed. The second rule of effective dashboard design involves displaying data that meets one or more business or organizational objectives. To accomplish this, the elements in a dashboard should convey a format easily understood by its intended audience. Attendees learn how to create dynamic, interactive user- and data-driven dashboards, graphical and table-driven dashboards, statistical dashboards, and drill-down dashboards with a purpose.

With the latest release of SAS^® Business Rules Manager, decision-making using SAS^® Stored Processes is now easier with simplified deployment via a web service for integration with your applications and business processes. This paper shows you how a user can publish analytics and rules as SOAP-based web services, track its usage, and dynamically update these decisions using SAS Business Rules Manager. In addition, we demonstrate how to integrate with SAS^® Model Manager using SAS^® Workflow to demonstrate how your other SAS^® applications and solutions can also simplify real-time decision-making through business rules.

Programmers can create keyboard macros to perform common editing tasks in SAS^® Enterprise Guide^®. This paper introduces how to record keystrokes, save a keyboard macro, edit the commands, and assign a shortcut key. Sample keyboard macros are included. Techniques to share keyboard macros are also covered.

Read the paper (PDF). | Download the data file (ZIP).

When faced with a difficult data reduction problem, a SAS^® programmer has many options for how to solve the problem. In this presentation, three different methods are reviewed and compared in terms of processing time, debugging, and ease of understanding. The three methods include linearizing the data, using SQL Cartesian joins, and using sequential data processing. Inconsistencies in the raw data caused the data linearization to be problematic. The large number of records and the need for many-to-many merges resulted in a long run time for the SQL code. The sequential data processing, although older technology, provided the most time efficient and error-free results.

SAS^® Enterprise Guide^® is a great interface for businesses running SAS^® in a shared server environment. However, interacting with the shared server outside of SAS can require costly third-party software and knowledge of specific server programming languages. This can create a barrier between the SAS program and the server, which can be frustrating for even the best SAS programmers. This paper reviews the X and SYSTASK commands and creates a template of SAS code to pass commands from SAS to the server. By writing the server log to a text file, we demonstrate how to display critical server information in the code results. Using macros and the prompt functionality of SAS Enterprise Guide, we form stored procedures, allowing SAS users of all skill levels to interact with the server environment. These stored procedures can improve programming efficiency by providing a quick in-program solution to complete common server tasks such as copying folders or changing file permissions. They might also reduce the need for third-party programs to communicate with the server, which could potentially reduce software costs.

Data access collisions occur when two or more processes attempt to gain concurrent access to a single data set. Collisions are a common obstacle to SAS^® practitioners in multi-user environments. As SAS instances expand to infrastructures and ultimately empires, the inherent increased complexities must be matched with commensurately higher code quality standards. Moreover, permanent data sets will attract increasingly more devoted users and automated processes clamoring for attention. As these dependencies increase, so too does the likelihood of access collisions that, if unchecked or unmitigated, lead to certain process failure. The SAS/SHARE^® module offers concurrent file access capabilities, but causes a (sometimes dramatic) reduction in processing speed, must be licensed and purchased separately from Base SAS^®, and is not a viable solution for many organizations. Previously proposed solutions in Base SAS use a busy-wait spinlock cycle to repeatedly attempt file access until process success or timeout. While effective, these solutions are inefficient because they generate only read-write locked data sets that unnecessarily prohibit access by subsequent read-only requests. This presentation introduces the %LOCKITDOWN macro that advances previous solutions by affording both read-write and read-only lock testing and deployment. Moreover, recognizing the responsibility for automated data processes to be reliable, robust, and fault tolerant, %LOCKITDOWN is demonstrated in the context of a macro-based exception handling paradigm.

A SAS^® Grid Manager environment provides your organization with a powerful and flexible way to manage many forms of SAS^® computing workloads. For the business and IT user community, the benefits can range from data management jobs effectively utilizing the available processing resources, complex analyses being run in parallel, and reassurance that statutory reports are generated in a highly available environment. This workshop begins the process of familiarizing users with the core concepts of how to grid-enable tasks within SAS^® Studio, SAS^® Enterprise Guide^®, SAS^® Data Integration Studio, and SAS^® Enterprise Miner™ client applications.

Learning the Graph Template Language (GTL) might seem like a daunting task. However, creating customized graphics with SAS^® is quite easy using many of the tools offered with Base SAS^® software. The point-and-click interface of ODS Graphics Designer provides us with a tool that can be used to generate highly polished graphics and to store the GTL-based code that creates them. This opens the door for users who would like to create canned graphics that can be used on various data sources, variables, and variable types. In this hands-on training, we explore the use of ODS Graphics Designer to create sophisticated graphics and to save the template code. We then discuss modifications using basic SAS macros in order to create stored graphics code that is flexible enough to accommodate a wide variety of situations.

Do you have a SAS^® program that requires adding filenames to the input every time you run it? Aren't you tired of having to check for the files, check the names, and type them in? Check out how my SAS^® Enterprise Guide^® project checks for files, figures out the file names, and saves me from having to type in the file names for the input data files!

Read the paper (PDF). | Download the data file (ZIP).

When ODS Graphics was introduced a few years ago, it gave SAS users an array of new ways to generate graphs. One of those ways is with Statistical Graphics procedures. Now, with just a few lines of simple code, you can create a wide variety of high-quality graphs. This paper shows how to produce single-celled graphs using PROC SGPLOT and paneled graphs using PROC SGPANEL. This paper also shows how to send your graphs to different ODS destinations, how to apply ODS styles to your graphs, and how to specify properties of graphs, such as format, name, height, and width.

Axis tables, polygon plot, text plot, and more features have been added to Statistical Graphics (SG) procedures and Graph Template Language (GTL) for SAS^® 9.4. These additions are a direct result of your feedback and are designed to make creating graphs easier. Axis tables let you add multiple tables of data to your graphs and to correctly align with the axis values with the right colors for group values in your data. Text plots can have rotated and aligned text anywhere in the graph. You can overlay jittered markers on box plots, use images and font glyphs as markers, specify group attributes without making style changes, and create entirely new custom graphs using the polygon plot. All this without using the annotation facility, which is now supported both for SG procedures and GTL. This paper guides you through these exciting new features now available in SG procedures and GTL.

Read the paper (PDF). | Download the data file (ZIP).

The SAS^® Macro Language is a powerful tool for extending the capabilities of the SAS^® System. This hands-on workshop teaches essential macro coding concepts, techniques, tips, and tricks to help beginning users learn the basics of how the macro language works. Using a collection of proven macro language coding techniques, attendees learn how to write and process macro statements and parameters; replace text strings with macro (symbolic) variables; generate SAS code using macro techniques; manipulate macro variable values with macro functions; create and use global and local macro variables; construct simple arithmetic and logical expressions; interface the macro language with the SQL procedure; store and reuse macros; troubleshoot and debug macros; and develop efficient and portable macro language code.

SAS^® users are already familiar with the FCMP procedure and the flexibility it provides them in writing their own functions and subroutines. However, did you know that FCMP also allows you to call functions written in C? Did you know that you can create and populate complex C structures and use C types in FCMP? With the PROTO procedure, you can define function prototypes, structures, enumeration types, and even small bits of C code. This paper gets you started on how to use the PROTO procedure and, in turn, how to call your C functions from within FCMP and SAS.

Read the paper (PDF). | Download the data file (ZIP).

No matter what type of programming you do in a pharmaceutical environment, there will eventually be a need to combine your data with a lookup table. This lookup table could be a code list for adverse events, a list of names for visits, one of your own summary data sets containing totals that you will be using to calculate percentages, or you might have your favorite way to incorporate it. This paper describes and discusses the reasons for using five different simple ways to merge data sets with lookup tables, so that when you take over the maintenance of a new program, you will be ready for anything!

Storage space on a UNIX platform is a costly--and finite--resource to maintain, even under ideal conditions. By regularly monitoring and promptly responding to space limitations that might occur during production, an organization can mitigate the risk of wasted expense, time and effort caused by this problem. SAS^® programmers at Truven Health Analytics have designed a reporting tool to measure space usage by a number of distinct factors over time. Using tabular and graphical output, the tool provides a full picture of what often contributes to critical reductions of available hardware space. It enables managers and users to respond appropriately and effectively whenever this occurs. It also helps to identify ways to encourage more efficient practices, thereby minimizing the likelihood of this occurring in the future. Operating System: RHEL 5.4 (Red Hat Enterprise Linux), Oracle Sun Fire X4600 M2 SAS^® 9.3 TS1M1.

The widely used method to convert RSR XML data to some standard, ready-to-process database uses a Visual Basic mapper as a buffer tool when reading XML data (for example, into an MS Access database). This paper describes the shortcomings of this method with respect to the different schemas of RSR data and offers a SAS^® macro that enables users to read any schema of RSR data directly into a SAS relational database. This macro entirely eliminates the step of creating an MS Access database. Using our macro, the user can cut the time of processing of Ryan White data by 99% and more, depending on the number of files that need to be processed in one run.

The DATA step has served SAS^® programmers well over the years, and although it is powerful, it has not fundamentally changed. With DS2, SAS introduced a significant alternative to the DATA step by providing an object-oriented programming environment. In this paper, we share our experiences with getting started with DS2 and learning to use it to access, manage, and share data in a scalable, threaded, and standards-based way.

Why did my merge fail? How did that variable get truncated? Why am I getting unexpected results? Understanding how the DATA step actually works is the key to answering these and many other questions. In this paper, two independent consultants with a combined three decades of SAS^® programming experience share a treasure trove of knowledge aimed at helping the novice SAS programmer take his or her game to the next level by peering behind the scenes of the DATA step. We touch on a variety of topics, including compilation versus execution, the program data vector, and proper merging techniques, with a focus on good programming practices that help the programmer steer clear of common pitfalls.

In SAS^® software development, data specifications and process requirements can be built into user-defined control data set functioning as components of ETL routines. A control data set provides comprehensive definition on the data source, relationship, logic, description, and metadata of each data element. This approach facilitates auto-generated SAS codes during program execution to perform data ingestion, transformation, and loading procedures based on rules defined in the table. This paper demonstrates the application of using a control data set for the following: (1) data table initialization and integration; (2) validation and quality control; (3) element transformation and creation; (4) data loading; and (5) documentation. SAS programmers and business analysts would find programming development and maintenance of business rules more efficient with this standardized method.

This presentation teaches the audience how to use ODS Graphics. Now part of Base SAS^®, ODS Graphics are a great way to easily create clear graphics that enable any user to tell their story well. SGPLOT and SGPANEL are two of the procedures that can be used to produce powerful graphics that used to require a lot of work. The core of the procedures is explained, as well as some of the many options available. Furthermore, we explore the ways to combine the individual statements to make more complex graphics that tell the story better. Any user of Base SAS on any platform will find great value in the SAS ODS Graphics procedures.

The SAS^® hash object is an incredibly powerful technique for integrating data from two or more data sets based on a common key. This session describes the basic methodology for defining, populating, and using a hash object to perform lookups within the DATA step and provides examples of situations in which the performance of SAS programs is improved by their use. Common problems encountered when using hash objects are explained, and tools and techniques for optimizing hash objects within your SAS program are demonstrated.

Examples include: how to join when your data is perfect, how to join when your data does not match and you have to manipulate it in order to join, how to create a subquery, how to use a subquery.

From stock price histories to hospital stay records, analysis of time series data often requires the use of lagged (and occasionally lead) values of one or more analysis variables. For the SAS^® user, the central operational task is typically getting lagged (lead) values for each time point in the data set. Although SAS has long provided a LAG function, it has no analogous lead function--an especially significant problem in the case of large data series. This paper reviews the LAG function (in particular, the powerful but non-intuitive implications of its queue-oriented basis), demonstrates efficient ways to generate leads with the same flexibility as the LAG function (but without the common and expensive recourse of data re-sorting), and shows how to dynamically generate leads and lags through the use of the hash object.

Read the paper (PDF). | Download the data file (ZIP).

Across the languages of SAS^® are many golden nuggets--functions, formats, and programming features just waiting to impress your friends and colleagues. Learning SAS over 30+ years, I have collected a few, and I offer them to you in this presentation.

A forest plot is a common visualization for meta-analysis. Some popular versions that use subgroups with indented text and bold fonts can seem outright daunting to create. With SAS^® 9.4, the Graph Template Language (GTL) has introduced the AXISTABLE statement, specifically designed for including text data columns into a graph. In this paper, we demonstrate the simplicity of creating various forest plots using AXISTABLE statements. Come and see how to create forest plots as clear as day!

Read the paper (PDF). | Download the data file (ZIP).

SAS^® provides a number of tools for creating customized professional reports. While SAS provides point-and-click interfaces through products such as SAS^® Web Report Studio, SAS^® Visual Analytics or even SAS^® Enterprise Guide^®, unfortunately, many users do not have access to the high-end tools and require customization beyond the SAS Enterprise Guide point-and-click interface. Fortunately, base SAS procedures such as the REPORT procedure, combined with graphics procedures, macros, ODS, and Annotate can be used to create very customized professional reports. When toggling together different solutions such as SAS Statistical Graphics, the REPORT procedure, ODS, and SAS/GRAPH^®, different techniques need to be used to keep the same look and feel throughout the report package. This presentation looks at solutions that can be used to keep a consistent look and feel in a report package created with different SAS products.

File management is a tedious process that can be automated by using SAS^® to create and execute a Windows command script. The macro in this paper copies files from one location to another, identifies obsolete files by the version number, and then moves them to an archive folder. Assuming that some basic conditions are met, this macro is intended to be easy to use and robust. Windows users who run routine programs for projects with rework might want to consider this solution.

Qualtrics is an online survey tool that offers a variety of features useful to researchers. In this paper, we show you how to implement the different options available for distributing surveys and downloading survey responses. We use the FILENAME statement (URL access method) and process the API responses with SAS^® XML Mapper. In addition, we show an approach for how to keep track of active and inactive respondents.

This paper describes the main functions of the SAS^® SG procedures and their relations. It also offers a way to create data-colored maps using these procedures. Here are the basics of the SG procedures. For a few years, the SG procedures (PROC SGPLOT, PROC SGSCATTER, PROC SGPANEL, and so on) have been part of Base SAS^® and thus available for everybody. SG originated as Statistical Graphics , but nowadays the procedures are often referred to as SAS^® ODS Graphics. With the syntax in a 1000+ page document, it is quite a challenge to start using them. Also, SAS^® Enterprise Guide^® currently has no graphics tasks that generate code for the SG procedures (except those in the statistical arena). For a long time SAS/GRAPH^® has been the vehicle for producing presentation-ready graphs of your data. In particular, the SAS users that have experience with those SAS/GRAPH procedures will hesitate to change over. But the SG procedures continue to be enhanced with new features. And, because the appearance of many elements is governed by the ODS styles, they are very well suited to provide a consistent style across all your output, text and graphics. PROC SGPLOT - PROC SGPANEL - PROC SGSCATTER: The paper first describes the basic procedures that a user will start with; PROC SGPLOT is the first place. Then the more elaborate possibilities of PROC SGPANEL and PROC SGSCATTER are described. Both these procedures can create a matrix or panel of graphs. The different goals of these two procedures will be explained: comparing a group of variables versus comparing the levels of two variables. PROC SGPLOT can create many different graphs: histograms, time series, scatterplots, and so on. PROC SGPANEL has essentially the same possibilities. The nature of PROC SGSCATTER (and the name says it already) limits it to scatter-like graphs. But many statements and options are common to lots of types of graphs. This paper groups them logically , making clear what the procedures have in common and where they differ. Related to the SG procedures are also two utilities (the Graphics Editor and the Graphics Designer), which are delivered as SAS^® Foundation applications. The paper describes the relations between these utilities and the objects they produce, and the relevant SG procedures and related utilities. Creating a map for virtually all tasks that can be performed with the well known SAS/GRAPH procedures, the counterpart in the SG procedures is easily pointed out, often with more extensive features. This is not the case for the maps produced with PROC GMAP. This paper shows the mere few steps that are necessary to convert the data sets that contain your data and your map coordinates into data sets that enable you to use the power and features of PROC SGPLOT to create your map in any projection system and any coordinate window.

Read the paper (PDF). | Download the data file (ZIP).

You might be familiar with or experienced in writing or running reports using PROC REPORT, PROC TABULATE, or other methods of report generation. These reporting methods are often very flexible, but they can be limited in the statistics that are available as options for inclusion in the resulting output. SAS^® provides the capability to produce a variety of statistics through Base SAS^® and SAS/STAT^® procedures by using ODS OUTPUT. These procedures include statistics from PROC CORR, PROC FREQ, and PROC UNIVARIATE in Base SAS, as well as PROC GLM, PROC LIFETEST, PROC MIXED, PROC LOGISTIC, and PROC TTEST in SAS/STAT. A number of other procedures can also produce useful ODS OUTPUT objects. Commonly requested statistics for reports include p-values, confidence intervals, and test statistics. These values can be computed with the appropriate procedure, and then use ODS OUTPUT to output the desired information to a data set and include the new information with the other data used to produce the report. Examples that demonstrate how to easily generate the desired statistics or other information and include it to produce the requested final reports are provided and discussed.

This paper describes the new features added to the macro facility in SAS^® 9.3 and SAS^® 9.4. New features described include the /READONLY option for macro variables, the %SYSMACEXIST macro function, the %PUT &= feature, and new automatic macro variables such as &SYSTIMEZONEOFFSET.

The DS2 programming language was introduced as part of the SAS^® 9.4 release. Although this new language introduced many significant advancements, one of the most overlooked features is the addition of object-oriented programming constructs. Specifically, the addition of user-defined packages and methods enables programmers to create their own objects, greatly increasing the opportunity for code reuse and decreasing both development and QA duration. In addition, using this object-oriented approach provides a powerful design methodology where objects closely resemble the real-world entities that they model, leading to programs that are easier to understand and maintain. This paper introduces the object-oriented programming paradigm in a three-step manner. First, the key object-oriented features found in the DS2 language are introduced, and the value each provides is discussed. Next, these object-oriented concepts are demonstrated through the creation of a blackjack simulation where the players, the dealer, and the deck are modeled and coded as objects. Finally, a credit risk scoring object is presented to demonstrate the application of this approach in a real-world setting.

SAS^® data sets have PROC DATASETS, and SAS catalogs have PROC CATALOG. Find out what the little-known PROC CATALOG can do for you!

The task was to produce a figure legend that gave the quintile ranges of a continuous measure corresponding to each color on a five-color choropleth US map. Actually, we needed to produce the figures and associated legends for several dozen maps for several dozen different continuous measures and time periods, as well as create the associated alt text for compliance with Section 508. So, the process needed to be automated. A method was devised using PROC RANK to generate the quintiles, PROC SQL to get the data value ranges within each quintile, and PROC FORMAT (with the CNTLIN= option) to generate and store the legend labels. The resulting data files and format catalogs were used to generate both the maps (with legends) and associated alt text. Then, these processes were rolled into a macro to apply the method for the many different maps and their legends. Each part of the method is quite simple--even mundane--but together, these techniques enabled us to standardize and automate an otherwise very tedious process. The same basic strategy could be used whenever you need to dynamically generate data buckets and keep track of the bucket boundaries (for producing labels, map legends, or alt text or for benchmarking future data against the stored categories).

One of the fascinating features of SAS^® is that the software often provides multiple ways to accomplish the same task. A perfect example of this is the aggregation and summarization of data across multiple rows or BY groups of interest. These groupings can be study participants, time periods, geographical areas, or just about any type of discrete classification that you want. While many SAS programmers might be accustomed to accomplishing these aggregation tasks with PROC SUMMARY (or equivalently, PROC MEANS), PROC SQL can also do a bang-up job of aggregation--often with less code and fewer steps. This step-by-step paper explains how to use PROC SQL for a variety of summarization and aggregation tasks. It uses a series of concrete, task-oriented examples to do so. The presentation style issimilar to that used in the author's previous paper, PROC SQL for DATA Step Die-Hards.'

Do you have reports based on SAS/GRAPH^® procedures, customized with multiple GOPTIONS? Do you dream of those same graphs existing in a GOPTIONS and ANNOTATE-free world? Re-creating complex graphs using statistical graphics (SG) procedures is not only possible, but much easier than you think! Using before and after examples, I discuss how the graphs were created using the combination of Graph Template Language (GTL) and the SG procedures. This method produces graphs that are nearly indistinguishable from the original. This method simplifies the code required to make complex graphs, allows for maximum re-usability for graphics code, and enables changes to cascade to multiple reports simultaneously.

Many scientific and academic journals require that statistical tables be created in a specific format, with one of the most common formats being that of the American Psychological Association (APA). The APA publishes a substantial guide book to writing and formatting papers, including an extensive section on creating tables (Nichol 2010). However, the output generated by SAS^® procedures does not match this style. This paper discusses techniques to change the SAS procedure output to match the APA guidelines using SAS ODS (Output Delivery System).

A bubble map can be a useful tool for identifying trends and visualizing the geographic proximity and intensity of events. This session shows how to use PROC GEOCODE and PROC GMAP to turn a data set of addresses and events into a map of the United States with scaled bubbles depicting the location and intensity of the events.

Although it does not happen every day, it is not unusual to need to place a quoted string within another quoted string. Fortunately, SAS^® recognizes both single and double quote marks and either can be used within the other, which gives you the ability to have two-deep quoting. There are situations, however, where two kinds of quotes are not enough. Sometimes you need a third layer or, more commonly, you need to use a macro variable within the layers of quotes. Macro variables can be especially problematic, because they generally do not resolve when they are inside single quotes. However, this is SAS and that implies that there are several things going on at once and that there are several ways to solve these types of quoting problems. The primary goal of this presentation is to assist the programmer with solutions to the quotes-within-quotes problem with special emphasis on the presence of macro variables. The various techniques are contrasted as are the likely situations that call for these types of solutions. A secondary goal of this presentation is to help you understand how SAS works with quote marks and how it handles quoted strings. Without going into the gory details, a high-level understanding can be useful in a number of situations.

REST is being used across the industry for designing networked applications to provide lightweight and powerful alternatives to web services such as SOAP and Web Services Description Language (WSDL). Since REST is based entirely around HTTP, SAS^® provides everything you need to make REST calls and process structured and unstructured data alike. Learn how PROC HTTP and other SAS language features provide everything you need to simply and securely make use of REST.

As a part of regulatory compliance requirements, banks are required to submit reports based on Microsoft Excel, as per templates supplied by the regulators. This poses several challenges, including the high complexity of templates, the fact that implementation using ODS can be cumbersome, and the difficulty in keeping up with regulatory changes and supporting dynamic report content. At the same time, you need the flexibility to customize and schedule these reports as per your business requirements. This paper discusses an approach to building these reports using SAS^® XML Mapper and the Excel XML spreadsheet format. This approach provides an easy-to-use framework that can accommodate template changes from the regulators without needing to modify the code. It is implemented using SAS^® technologies, providing you the flexibility to customize to your needs. This approach also provides easy maintainability.

The IN operator within the DATA step is used for searching a specific variable for some values, either numeric or character (for example, 'if X in (2), then...'). This brief note explains how the opposite situation can be managed. That is, it explains how to search for a specific value in several variables through applying an array and the IN operator together.

It is a safe assumption that almost every SAS^® user learns how to use the SET statement not long after they're taught the concept of a DATA step. Further, it would probably be reasonable to guess that almost everyone of those people covered the MERGE statement soon afterwards. Many, maybe most, also got to try the UPDATE and/or MODIFY statements eventually, as well. It would also be a safe assumption that very few people have taken the time to review the manual since they've learned about those statements. That is most unfortunate, because there are so many options available to the users that can assist them in their tasks of obtaining and combining data sets. This presentation is designed to build onto the basic understanding of SET, MERGE, and UPDATE. It assumes that the attendee or reader has a basic knowledge of those statements, and it introduces various options and usages that extend the utility of these basic commands.

SAS^® formats can be used in so many different ways! Even the most basic SAS format use (modifying the way a SAS data value is displayed without changing the underlying data value) holds a variety of nifty tricks, such as nesting formats, formats that affect various style attributes, and conditional formatting. Add in picture formats, multi-label formats, using formats for data cleaning, and formats for joins and table look-ups, and we have quite a bag of tricks for the humble SAS format and PROC FORMAT, which are used to generate them. This paper describes a few very useful programming techniques that employ SAS formats. While this paper will be appropriate for the newest SAS user, it will also focus on some of the lesser-known features of formats and PROC FORMAT and so should be useful for even quite experienced users of SAS.

Can you create hundreds of great looking Microsoft Excel tables all within SAS^® and make them all Section 508 compliant at the same time? This paper examines how to use the ODS TAGSETS.EXCELXP statement and other Base SAS^® features to create fantastic looking Excel worksheet tables that are all Section 508 compliant. This paper demonstrates that there is no need for any outside intervention or pre- or post-meddling with the Excel files to make them Section 508 compliant. We do it all with simple Base SAS code.

Customer expectations are set high when Microsoft Excel and Microsoft PowerPoint are used to design reports. Using SAS^® for reporting has benefits because it generates plots directly from prepared data sets, automates the plotting process, minimizes labor-intensive manual construction using Microsoft products, and does not compromise the presentation value. SAS^® Enterprise Guide^® 5.1 has a powerful point-and-click method that is quick and easy to use. However, it is limited in its ability to customize the output to mimic manually created Microsoft graphics. This paper demonstrates why SAS Enterprise Guide is the perfect starting point for creating initial code for plots using SAS/GRAPH^® point-and-click features and how the code can be enhanced using established PROC GPLOT, ANNOTATE, and ODS options to re-create the look and feel of plots generated by Excel and PowerPoint. Examples show the generation of plots and tables using PROC TABULATE to embed the plot data into the graphical output. Also included are tips for overcoming the ODS limitation of SAS^® 9.3, which is used by SAS Enterprise Guide 5.1, to transfer the SAS graphical output to PowerPoint files. These SAS^® 9.3 tips are contrasted with the new SAS^® 9.4 ODS POWERPOINT statement that enables direct PowerPoint file creation from a SAS program.

A good system should embody the following characteristics: planned, maintainable, flexible, simple, accurate, restartable, reliable, reusable, automated, documented, efficient, modular, and validated. This is true of any system, but how to implement this in SAS^® Enterprise Guide^® is a unique endeavor. We provide a brief overview of these characteristics and then dive deeper into how a SAS Enterprise Guide user should approach developing both ad hoc and production systems.

SAS^® Studio (previously known as SAS^® Web Editor) was introduced in the first maintenance release of SAS^® 9.4 as an alternative programming environment to SAS^® Enterprise Guide^® and SAS^® Display Manager. SAS Studio is different in many ways from SAS Enterprise Guide and SAS Display Manager. As a programmer, I currently use SAS Enterprise Guide to help me code, test, maintain, and organize my SAS^® programs. I have SAS Display Manager installed on my PC, but I still prefer to write my programs in SAS Enterprise Guide because I know it saves my log and output whenever I run a program, even if that program crashes and takes the SAS session with it! So should I now be using SAS Studio instead, and should you be using it, too?

Six Sigma is a business management strategy that seeks to improve the quality of process outputs by identifying and removing the causes of defects (errors) and minimizing variability in manufacturing and business processes. Each Six Sigma project carried out within an organization follows a defined sequence of steps and has quantified financial targets. All Six Sigma project methodologies include an extensive analysis phase in which SAS^® software can be applied. JMP^® software is widely used for Six Sigma projects. However, this paper demonstrates how Base SAS^® (and a bit of SAS/GRAPH^® and SAS/STAT^® software) can be used to address a wide variety of Six Sigma analysis tasks. The reader is assumed to have a basic knowledge of Six Sigma methodology. Therefore, the focus of the paper is the use of SAS code to produce outputs for analysis.

Text messages (SMS) are a convenient way to receive notifications away from your computer screen. In SAS^®, text messages can be sent to mobile phones via the DATA step. This paper briefly describes several methods for sending text messages from SAS and explores possible applications.

SAS^® functions provide amazing power to your DATA step programming. Specific functions are essential--they save you from writing volumes of unnecessary code. This presentation covers some of the most useful SAS functions. A few might be new to you and they can all change how you program and approach common programming tasks. The majority of these functions work with character data. There are functions that search for strings, others that find and replace strings, and some that join strings together. Furthermore, certain functions can measure the spelling distance between two strings (useful for fuzzy matching). Some of the newest and most incredible functions are not functions at all--they are call routines. Did you know that you can sort values within an observation? Did you know that not only can you identify the largest or smallest value in a list of variables, but you can identify the second or third or nth largest or smallest value? A knowledge of these functions will make you a better SAS programmer.

Your enterprise SAS^® Visual Analytics implementation is on its way to being adopted throughout your organization, unleashing the production of critical business content by business analysts, data scientists, and decision makers from many business units. This content is relied upon to inform decisions and provide insight into the results of those decisions. With the development of SAS Visual Analytics content decentralized into the hands of business users, the use of automated version control is essential to providing protection and recovery in the event of inadvertent changes to that content. Re-creation of complex report objects accidentally modified by a business user is time-consuming and can be eliminated by maintaining a version control repository of report (and other) objects created in SAS Visual Analytics. This paper walks through the steps for implementing an automated process for version control using SAS^®. This process can be applied to all types of metadata objects used in multiple SAS application development and analysis environments, such as reports and explorations from SAS Visual Analytics, and jobs, tables, and libraries from SAS^® Data Integration Studio. Basic concepts for the process, as well as specific techniques used for our implementation are included. So eliminate the risk of content loss for your business users and the burden of manual version control for your applications developers. Your IT shop will enjoy time savings and greater reliability.

The report looks simple enough--a bar chart and a table, like something created with GCHART and REPORT procedures. But, there are some twists to the reporting requirements that make those procedures not quite flexible enough. The solution was to mix 'old' and 'new' DATA step-based techniques to solve the problem. Annotate datasets are used to create the bar chart and the Report Writing Interface (RWI) to create the table. Without a whole lot of additional code, an extreme amount of flexibility is gained.

Can you actually get something for nothing? With the SAS^® PROC SQL subquery and remerging features, yes, you can. When working with categorical variables, you often need to add group descriptive statistics such as group counts and minimum and maximum values for further BY-group processing. Instead of first creating the group count and minimum or maximum values and then merging the summarized data set to the original data set, why not take advantage of PROC SQL to complete two steps in one? With the PROC SQL subquery and summary functions by the group variable, you can easily remerge the new group descriptive statistics with the original data set. Now with a few DATA step enhancements, you too can include percent calculations.

Technology is always changing. To succeed in this ever-evolving landscape, organizations must embrace the change and look for ways to use it to their advantage. Even standard business tasks such as creating reports are affected by the rapid pace of technology. Reports are key to organizations and their customers. Therefore, it is imperative that organizations employ current technology to provide data in customized and meaningful reports across a variety of media. The SAS^® Output Delivery System (ODS) gives you that edge by providing tools that enable you to package, present, and deliver report data in more meaningful ways, across the most popular desktop and mobile devices. To begin, the paper illustrates how to modify styles in your reports using the ODS CSS style engine, which incorporates the use of cascading style sheets (CSS) and the ODS document object model (DOM). You also learn how you can use SAS ODS to customize and generate reports in the body of e-mail messages. Then the paper discusses methods for enhancing reports and rendering them in desktop and mobile browsers by using the HTML and HTML5 ODS destinations. To conclude, the paper demonstrates the use of selected SAS ODS destinations and features in practical, real-world applications.

SAS^® provides a complex ecosystem with multiple tools and products that run in a variety of environments and modes. SAS provides numerous error-handling and program control statements, options, and features. These features can function differently according to the run environment, and many of them have constraints and limitations. In this presentation, we review a number of potential error-handling and program control strategies that can be employed, along with some of the inherent limitations of each. The bottom line is that there is no single strategy that will work in all environments, all products, and all run modes. Instead, programmers need to consider the underlying program requirements and choose the optimal strategy for their situation.

Read the paper (PDF). | Download the data file (ZIP).

SAS^® users organize their applications in a variety of ways. However, there are some approaches that are more successful, and some that are less successful. In particular, the need to process some of the code some of the time in a file is sometimes challenging. Reproducible research methods require that SAS applications be understandable by the author and other staff members. In this presentation, you learn how to organize and structure your SAS application to manage the process of data access, data analysis, and data presentation. The approach to structure applications requires that tasks in the process of data analysis be compartmentalized. This can be done using a well-defined program. The author presents his structuring algorithm, and discusses the characteristics of good structuring methods for SAS applications. Reproducible research methods are becoming more centrally important, and SAS users must keep up with the current developments.

Read the paper (PDF). | Download the data file (ZIP).

One of the more commonly needed operations in SAS^® programming is to determine the value of one variable based on the value of another. A series of techniques and tools have evolved over the years to make the matching of these values go faster, smoother, and easier. A majority of these techniques require operations such as sorting, searching, and comparing. As it turns out, these types of techniques are some of the more computationally intensive. Consequently, an understanding of the operations involved and a careful selection of the specific technique can often save the user a substantial amount of computing resources. Many of the more advanced techniques can require substantially fewer resources. It is incumbent on the user to have a broad understanding of the issues involved and a more detailed understanding of the solutions available. Even if you do not currently have a BIG data problem, you should at the very least have a basic knowledge of the kinds of techniques that are available for your use.

How many times has this happened to you? You create a really helpful report and share it with others. It becomes popular and you find yourself running it over and over. Then they start asking, But can't you re-run it and just change ___? (Fill in the blank with whatever simple request you can think of.) Don't you want to just put the report out as a web page with some basic parameters that users can choose themselves and run when they want? Consider writing your own task in SAS^® Studio! SAS Studio includes several predefined tasks, which are point-and-click user interfaces that guide the user through an analytical process. For example, tasks enable users to create a bar chart, run a correlation analysis, or rank data. When a user selects a task option, SAS^® code is generated and run on the SAS server. Because of the flexibility of the task framework, you can make a copy of a predefined task and modify it or create your own. Tasks use the same common task model and the Velocity Template Language--no Java programming or ActionScript programming is required. Once you have the interface set up to generate the SAS code you need, then you can publish the task for other SAS Studio users to use or you can use a straight URL. Now that others can generate the output themselves, you actually might have time to go fishing!

Many languages, including the SAS DATA step, have extensive debuggers that can be used to detect logic errors in programs. Another easy way to detect logic errors is to simply display messages and variable content at strategic times. The PUTLOG statement will be discussed and examples given that show how using this statement is probably the easiest and most flexible way to detect and correct errors in your DATA step logic.

The first thing that you need to know is that SAS^® software stores dates and times as numbers. However, this is not the only thing that you need to know, and this presentation gives you a solid base for working with dates and times in SAS. It also introduces you to functions and features that enable you to manipulate your dates and times with surprising flexibility. This paper also shows you some of the possible pitfalls with dates (and with times and datetimes) in your SAS code, and how to avoid them. We show you how SAS handles dates and times through examples, including the ISO 8601 formats and informats, and how to use dates and times in TITLE and FOOTNOTE statements. We close with a brief discussion of Microsoft Excel conversions.

Read the paper (PDF). | Download the data file (ZIP).

The knight's tour is a sequence of moves on a chess board such that a knight visits each square only once. Using a heuristic method, it is possible to find a complete path, beginning from any arbitrary square on the board and landing on the remaining squares only once. However, the implementation poses challenging programming problems. For example, it is necessary to discern viable knight moves, which change throughout the tour. Even worse, the heuristic approach does not guarantee a solution. This paper explains a SAS^® solution that finds a knight's tour beginning from every initial square on a chess board...well, almost.

It is well-known in the world of SAS^® programming that the REPORT procedure is one of the best procedures for creating dynamic reports. However, you might not realize that the compute block is where all of the action takes place! Its flexibility enables you to customize your output. This paper is a primer for using a compute block. With a compute block, you can easily change values in your output with the proper assignment statement and add text with the LINE statement. With the CALL DEFINE statement, you can adjust style attributes such as color and formatting. Through examples, you learn how to apply these techniques for use with any style of output. Understanding how to use the compute-block functionality empowers you to move from creating a simple report to creating one that is more complex and informative, yet still easy to use.

If you are one of the many customers who want to move your SAS^® data to Hadoop, one decision you will encounter is what data storage format to use. There are many choices, and all have their pros and cons. One factor to consider is how you currently store your data. If you currently use the Base SAS^® engine or the SAS^® Scalable Performance Data Engine, then using the SPD Engine with Hadoop will enable you to continue accessing your data with as little change to your existing SAS programs as possible. This paper discusses the enhancements, usage, and benefits of the SPD Engine with Hadoop.

There are many 'gotcha's' when you are trying to automate a well-written program. The details differ depending on the way you schedule the program and the environment you are using. This paper covers system options, error handling logic, and best practices for logging. Save time and frustration by using these tips as you schedule programs to run.

The Work library is at the core of most SAS^® programs, but programmers tend to ignore it unless something breaks. This paper first discusses the USER= system option for saving the Work files in a directory. Then, we cover a similar macro-controlled method for saving the files in your Work library, and the interaction of this method with OPTIONS NOREPLACE and the syntax check options. A number of SAS system options that help you to manage Work libraries are discussed: WORK=, WORKINIT, WORKTERM; these options might be restricted by SAS Administrators. Additional considerations in managing Work libraries are discussed: handling large files, file compression, programming style, and macro-controlled deletion of redundant files in SAS^® Enterprise Guide^®.

Read the paper (PDF). | Download the data file (ZIP).

The SAS^® macro processor is a powerful ally, but it requires respect. There are a myriad of macro functions available, most of which emulate DATA step functions, but some of which require special consideration to fully use their capabilities. Questions to be answered include the following: When should you protect the macro variable? During macro compilation, during macro execution? (What do those phrases even mean?) How do you know when to use which macro function? %BQUOTE(), %NBRQUOTE(), %UNQUOTE(),%SUPERQ(), and so on? What's with the %Q prefix of some macro functions? And more: %SYSFUNC(), %SYSCALL, and so on. Macro developers will no longer by daunted by the complexity of choices. With a little clarification, the power of these macro functions will open up new possibilities.

Using PROC TRANSPOSE to make wide files wider requires running separate PROC TRANSPOSE steps for each variable that you want transposed, as well as a DATA step using a MERGE statement to combine all of the transposed files. In addition, if you want the variables in a specific order, an extra DATA step is needed to rearrange the variable ordering. This paper presents a method that accomplishes the task in a simpler manner using less code and requiring fewer steps, and which runs n times faster than PROC TRANSPOSE (where n=the number of variables to be transposed).

Read the paper (PDF). | Download the data file (ZIP).

We've all heard it before: 'If two ampersands don't work, add a third.' But how many of us really know how ampersands work behind the scenes? We show the function of multiple ampersands by going through examples of the common two- and three-ampersand scenarios, and expand to show four, five, six, and even seven ampersands, and explain when they might be (rarely) useful.

There have been many SAS^® Global Forum papers written about getting your data into SAS^® from Microsoft Excel and getting your data back out to Excel after using SAS to manipulate it. But sometimes you have to update Excel files with special formatting and formulas that would be too hard to replicate with Dynamic Data Exchange (DDE) or that change too often to make DDE worthwhile. But we can still use the output prowess of SAS and a sprinkling of Visual Basic for Applications (VBA) to maintain the existing formatting and formulas in your Excel file! This paper focuses on the possibilities you have in updating Excel files by reading in the data, using SAS to modify it as needed, and then using DDE and a simple Excel VBA macro to output back to Excel and use a formula while maintaining the existing formatting that is present in your source Excel file.

Read the paper (PDF). | Download the data file (ZIP).

Many papers have been written over the years that describe how to use Dynamic Data Exchange (DDE) to pass data from SAS^® to Excel. This presentation aims to show you how to do the same exchange with the SAS Output Delivery System (ODS) and the TEMPLATE Procedure.

Read the paper (PDF). | Download the data file (ZIP).

If you have not had a chance to explore SAS^® Studio yet, or if you're anxious to see what's new, this paper gives you an introduction to this new browser-based interface for SAS^® programmers and a peek at what's coming. With SAS Studio, you can access your data files, libraries, and existing programs, and you can write new programs while using SAS software behind the scenes. SAS Studio connects to a SAS server in order to process SAS programs. The SAS server can be a hosted server in a cloud environment, a server in your local environment, or a copy of SAS on your local machine. It's a comfortable environment for those used to the traditional SAS windowing environment (SAS^® Display Manager) but new features like a query window, process flow diagrams, and tasks have been added to appeal to traditional SAS^® Enterprise Guide^® users.

Now that SAS^® users are moving to 64-bit Microsoft Windows platforms, some are discovering that vendor-supplied DLLs might still be 32-bit. Since 64-bit applications cannot use 32-bit DLLs, this would present serious technical issues. This paper explains how the MODULE routines in SAS can be used to call into 32-bit DLLs successfully, using new features added in SAS^® 9.3.

A familiar adage in firefighting--if you can predict it, you can prevent it--rings true in many circles of accident prevention, including software development. If you can predict that a fire, however unlikely, someday might rage through a structure, it's prudent to install smoke detectors to facilitate its rapid discovery. Moreover, the combination of smoke detectors, fire alarms, sprinklers, fire-retardant building materials, and rapid intervention might not prevent a fire from starting, but it can prevent the fire from spreading and facilitate its immediate and sometimes automatic extinguishment. Thus, as fire codes have grown to incorporate increasingly more restrictions and regulations, and as fire suppression gear, tools, and tactics have continued to advance, even the harrowing business of firefighting has become more reliable, efficient, and predictable. As operational SAS^® data processes mature over time, they too should evolve to detect, respond to, and overcome dynamic environmental challenges. Erroneous data, invalid user input, disparate operating systems, network failures, memory errors, and other challenges can surprise users and cripple critical infrastructure. Exception handling describes both the identification of and response to adverse, unexpected, or untimely events that can cause process or program failure, as well as anticipated events or environmental attributes that must be handled dynamically through prescribed, predetermined channels. Rapid suppression and automatic return to functioning is the hopeful end state but, when catastrophic events do occur, exception handling routines can terminate a process or program gracefully while providing meaningful execution and environmental metrics to developers both for remediation and future model refinement. This presentation introduces fault-tolerant Base SAS^® exception handling routines that facilitate robust, reliable, and responsible software design.

Working with multiple data sources in SAS^® was not a straight forward thing until PROC FEDSQL was introduced in the SAS^® 9.4 release. Federated Query Language, or FEDSQL, is a vendor-independent language that provides a common SQL syntax to communicate across multiple relational databases without having to worry about vendor-specific SQL syntax. PROC FEDSQL is a SAS implementation of the FEDSQL language. PROC FEDSQL enables us to write federated queries that can be used to perform joins on tables from different databases with a single query, without having to worry about loading the tables into SAS individually and combining them using DATA steps and PROC SQL statements. The objective of this paper is to demonstrate the working of PROC FEDSQL to fetch data from multiple data sources such as Microsoft SQL Server database, MySQL database, and a SAS data set, and run federated queries on all the data sources. Other powerful features of PROC FEDSQL such as transactions and FEDSQL pass-through facility are discussed briefly.

Managing and organizing external files and directories play an important part in our data analysis and business analytics work. A good file management system can streamline project management and file organizations and significantly improve work efficiency . Therefore, under many circumstances, it is necessary to automate and standardize the file management processes through SAS^® programming. Compared with managing SAS files via PROC DATASETS, managing external files is a much more challenging task, which requires advanced programming skills. This paper presents and discusses various methods and approaches to managing external files with SAS programming. The illustrated methods and skills can have important applications in a wide variety of analytic work fields.

Everyone likes getting a raise, and using ARRAYs in SAS^® can help you do just that! Using ARRAYs simplifies processing, allowing for reading and analyzing of repetitive data with minimum coding. Improving the efficiency of your coding and in turn, your SAS productivity, is easier than you think! ARRAYs simplify coding by identifying a group of related variables that can then be referred to later in a DATA step. In this quick tip, you learn how to define an ARRAY using an array statement that establishes the ARRAY name, length, and elements. You also learn the criteria and limitations for the ARRAY name, the requirements for array elements to be processed as a group, and how to call an ARRAY and specific array elements within a DATA step. This quick tip reveals useful functions and operators, such as the DIM function and using the OF operator within existing SAS functions, that make using ARRAYs an efficient and productive way to process data. This paper takes you through an example of how to do the same task with and without using ARRAYs in order to illustrate the ease and benefits of using them. Your coding will be more efficient and your data analysis will be more productive, meaning you will deserve ARRAYs!

How often have you pulled oodles of data out of the corporate data warehouse down into SAS^® for additional processing? This additional processing, sometimes thought to be uniquely SAS, might include FIRST. logic, cumulative totals, lag functionality, specialized summarization, or advanced date manipulation. Using the analytical (or OLAP) and Windowing functionality available in many databases (for example, in Teradata and IBM Netezza ), all of this processing can be performed directly in the database without moving and reprocessing detail data unnecessarily. This presentation illustrates how to increase your coding and execution efficiency by using the database's power through your SAS environment.