In this paper, a SAS^® macro is introduced that can search and replace any string in a SAS program. To use the macro, the user needs only to pass the search string to a folder. If the user wants to use the replacement function, the user also needs to pass the replacement string. The macro checks all of the SAS programs in the folder and subfolders to find out which files contain the search string. The macro generates new SAS files for replacements so that the old files are not affected. An HTML report is generated by the macro to include the original file locations, the line numbers of the SAS code that contain the search string, and the SAS code with search strings highlighted in yellow. If you use the replacement function, the HTML report also includes the location information for the new SAS files. The location information in the HTML report is created with hyperlinks so that the user can directly open the files from the report.

Read the paper (PDF) | View the e-poster or slides (PDF)

SAS/ACCESS^® software grants access to data in third-party database management systems (DBMS), but how do you access data in DBMS not supported by SAS/ACCESS products? The introduction of the GROOVY procedure in SAS^® 9.3 lets you retrieve this formerly inaccessible data through a JDBC connection. Groovy is an object-oriented, dynamic programming language executed on the Java Virtual Machine (JVM). Using Microsoft Azure HDInsight as an example, this paper demonstrates how to access and read data into a SAS data set using PROC GROOVY and a JDBC connection.

The SAS^® code looks perfect. You submit it and to your amazement, there is a problem with the CREATE TABLE statement. You need to change the table definition, ever so slightly, but how? Explicit pass-through? That's not an option. Fortunately, there are a handful of SAS options that can save the day. This presentation covers everything you need to know in order to adjust the SAS CREATE TABLE statements using SAS options. This presentation covers the following SAS options: DBCREATE_TABLE_OPTS=, POST_STMT_OPTS=, POST_TABLE_OPTS=, PRE_STMT_OPTS=, and PRE_TABLE_OPTS=. We use Hadoop and Oracle examples to show why these options can make your life easier. From there, we use real code to show you how to use them.

You have SAS^® software. You have databases or data platforms like Hadoop, possibly with some large distributed data. If you already know how to make SAS code talk to your data platforms, you have already taken a solid step toward a successful integration. But you might also want to know how to take this communication to a different level. If your data platform is the one that is built for massively parallel processing, chances are that SAS code has already created the SAS^® Embedded Process framework that allows SAS tasks to be embedded next to your data sources for execution. SAS^® In-Database Technologies is a family of products that use this framework and provide an accelerated level of integration. This paper explains core principles behind these technologies and presents application scenarios for each of these products. We use a variety of examples to highlight the specifics of individual SAS accelerators (SAS^® Scoring Accelerator, SAS^® In-Database Code Accelerator, and others) across the data platforms.

SAS^® Visual Analytics is a very powerful tool for users to visually explore data, but in some organizations not all data should be available for everybody. And although it is relatively easy to scale up a SAS Visual Analytics environment when the need for data increases, it still would be beneficial to set up a structure where the organization can keep control over who actually has the right to load data versus providing everybody the right to load data into a SAS Visual Analytics environment. Within this breakout session a potential solution is shown by providing a high-level overview of the SAS Visual Analytics data access management solution at ING bank in the Netherlands for the Risk Services Organization.

In this course you will learn how to access and manage SAS and Excel data in SAS^® Viya .

For many years now you have learned the ins and outs of using SAS/ACCESS^® software to move data into SAS^® to do your analytics. With the new open, cloud-ready SAS^® Viya platform comes a new set of data access technologies known as SAS data connectors and SAS data connect accelerators. This paper describes what these new data access products are and how they integrate with the SAS Viya platform. After reading this paper, you will have the foundation needed to load data from third-party data sources into SAS Viya.

In this paper, we explore advantages of the DS2 procedure over the DATA step programming in SAS^®. DS2 is a new SAS proprietary programming language appropriate for advanced data manipulation. We explore the use of PROC DS2 to execute queries in databases using SAS FedSQL. Several DS2 language elements accept embedded FedSQL syntax, and the run-time generated queries can exchange data interactively between DS2 and the supported database. This action enables SQL preprocessing of input tables, which effectively allows processing data from multiple tables in different databases within the same query, thereby drastically reducing processing times and improving performance. We explore use of DS2 for creating tables, bulk loading tables, manipulating tables, and querying data in an efficient manner. We explore advantages of using PROC DS2 over DATA step programming such as support for different data types, ANSI SQL types, programming structure elements, and benefits of using new expressions or writing one's own methods or packages available in the DS2 system. The DS2 procedure enables requests to be processed by the DS2 data access technology that supports a scalable, threaded, high-performance, and standards-based way to access, manage, and share relational data. In the end, we empirically measure performance benefits of using PROC DS2 over PROC SQL for processing queries in-database by taking advantage of threaded processing in supported databases such as Oracle.

There are so many ways for SAS/ACCESS^® users to read and write data from and to Microsoft Excel files: SAS^® PC Files Server, XLS and XLSX engines, the SAS IMPORT and EXPORT procedures, various Excel file formats (.xls, .xlsx, .xlsb, .xlsm), and more. Many users ask, 'Which is best for me?' This paper explores the requirements and limitations of each engine, along with performance considerations and some of the not-so-obvious things to consider. It also includes a brief analogous discussion on Microsoft Access databases, which share some of the same mechanisms.

As a SAS^® Visual Analytics administrator, how do you efficiently manage your SAS^® LASR environment? How do you ensure reliable data availability to your end users? How do you ensure that your users have the proper permissions to perform their tasks in SAS Visual Analytics? This paper covers some common management issues in SAS Visual Analytics, why and how they might arise, and how to resolve them. It discusses methods of programmatically managing your SAS^® LASR Analytic Server and tables, as well as using SAS^® Visual Analytics Administrator. Furthermore, it provides a better understanding of the roles in SAS Visual Analytics and demonstrates how to set up appropriate user permissions. Using the methods discussed in this paper can help you improve the end-user experience as well as system performance.

Using SAS^® to query relational databases can be challenging, even for seasoned SAS programmers. SAS/ACCESS^® software makes it easy to directly access data on nearly any platform, but there is a lot of under-the-hood functionality that takes time to learn. Here are tips that will get you on your way fast, including understanding and mastering SQL pass-through; efficiently bulk-loading data from SAS into other databases; tuning your SQL queries; and when to use native database versus SAS functionality.

If you run SAS^® and Teradata software with default application and database client encodings, some operations with international character sets will appear to work because you are actually treating character strings as streams of bytes instead of streams of characters. As long as no one in the chain of custody tries to interpret the data as anything other than a stream of bytes, then data can sometimes flow in and out of a database without being altered, giving the appearance of correct operation. But when you need to compare a particular character to an international character, or when your data approaches the maximum size of a character field, then you will run into trouble. To correctly handle international character sets, every layer of software that touches the data needs to agree on the encoding of the data. UTF-8 encoding is a flexible way to handle many single-byte and multi-byte international character sets. To use UTF-8 encoding with international character sets, we need to configure the SAS session encoding, Teradata client encoding, and Teradata server encoding to all agree, so that they are handling UTF-8 encoded data. This paper shows you how to configure SAS and Teradata so that your applications run successfully with international characters.