When SAS communicates with a database, efficient coding can mean the difference between a few minutes and a few hours! It is often easy to find tips on how to read data into and out of Excel spreadsheets, text files, and the like, but for users without experience in database administration or networking, getting SAS to talk to a database may seem like a trickier endeavor. Fortunately, SAS has some user-friendly functionality to establish connections, pull data, alter database contents, and take advantage of server resources. This paper will outline how to use ODBC and LIBNAME statements to connect to databases, issue basic PROC SQL commands to perform data selection and manipulation, and craft pass-through queries to have the database server do the heavy lifting for you.
Melissa R. Pfeiffer
Data Management Best Paper Winner
Data exchanges are increasingly common and are critical in propelling advancements in science and industry. However, these exchanges are not always seamless. Issues can arise both when receiving data from others and when distributing data, although the latter is usually easier. The aim of this paper is to provide guidance for both situations. For receiving data, we will discuss the importance of trying to obtain basic information about the data, such as a data dictionary or file layout with information about the field names, types, lengths, and meanings. At a bare minimum, recipients should ask about the number of records expected to confirm what is imported into SAS. We will discuss possible issues that may occur when importing non-SAS data into SAS and solutions. Finally, we will review some checks that can be conducted to confirm that files received are structurally sound. For distributing data, we will discuss strategies for creating non-SAS data, depending on what file type the recipient needs (e.g., Excel or CSV), whether column headings should be variable names or labels, and whether values should be formatted or unformatted. To help promote our reputations for giving generously, we will talk about documentation that should accompany the data you provide, such as a data dictionary – either simple or more detailed – and variable format information, when applicable. Collaborations and data sharing should be reasons for celebration rather than trepidation; the aim of this paper is to make these processes a little easier.
Melissa R. Pfeiffer and Kristi Metzger
Time and experience are your best tools for developing your SAS coding prowess; experience includes both working on a variety of tasks and working with different programmers. Through our combined 40 years working with SAS, we have worked on a wide assortment of projects with different data management needs and collaborators. Through these experiences we have learned to employ techniques that reduce the likelihood of introducing errors into our coding, help identify errors when they do appear, and generally make our programming lives easier. These tips include strategies for dealing with a growing number of user-defined formats, employing keyboard macros and %include statements for commonly used code, establishing standardized naming conventions, using arrays and do loops to cut down on coding, and cleaning up our “WORK” space. We all become better programmers when we share tips and strategies; our hope is that the suggestions we share expand your programming repertoire.
Some people should not move their SAS 9.4 into the cloud, and those who do, may run into difficulties. IT departments and consulting firms are learning cloud technology on the fly. Improper installations may cause SAS to run at one third of the speed it could run compared to on-premises. Furthermore, cloud technology can be expensive, and may cost hundreds of thousands of dollars per year more for a very large install, compared to keeping your SAS on-premises.
This paper was written for SAS programmers and IT departments alike. You want to move your SAS 9.4 installation into the cloud, and do it right… the first time! Choosing the most appropriate cloud compute instance type and model, and designing a proper cloud storage configuration can be challenging; done incorrectly, you will have a sub-standard or poorly performing SAS installation, resulting in a competitive disadvantage for your organization and frustrating your SAS users.
This paper defines terms, explains the bread-and-butter components of cloud technology, talks about SAS and Amazon Web Services architecture, covers SAS resource requirements, and presents a methodology for putting your SAS into the cloud, when it makes sense to do so, while being mindful of performance caveats. SAS programmers will gain a better understanding of the IT technologies required to run SAS, and IT professionals will gain better insights regarding SAS performance requirements and how to handle the cloud situation.
SAS has always lived and died on disk I/O throughput, and moving it into the cloud will not change that. If you do not get SAS I/O right, nothing else is going to work properly. This paper will emphasize ways to solve the I/O dilemma, so that a successful SAS cloud deployment is in the cards.