260-29: Starts and Stops: Processing Episode Data …

1 1 Paper 260-29 Starts and Stops: Processing Episode data with beginning and Ending Dates Mike Rhoads, Westat, Rockville, MD ABSTRACT Regardless of the industry you work in, you will probably encounter data involving things that begin and end on particular dates. People open and close banking and credit card accounts, enroll in and graduate from schools, are hired for and resign from jobs, are admitted to and discharged from hospitals. data files for these types of data typically include fields for person ID, beginning date, ending date, and various characteristics of the Episode . Answering questions involving this kind of data can be challenging for even experienced SAS programmers, to say nothing of beginners. How many days was this person employed during the previous year? Which records in this file have time periods that are inconsistent with each other?

2 Using data files with dates of insurance coverage and hospital stays, what insurance coverage does each patient have during each stay? This tutorial will provide examples of how SAS data steps and PROC SQL can be used to solve typical programming problems related to Episode data . The language constructs used in the examples will be explained so that relatively junior programmers should be able to follow and benefit from the examples. However, the techniques presented should also be useful to those with more SAS experience. INTRODUCTION This paper is somewhat different from most SAS conference tutorials. The typical approach is to focus on a set of SAS tools: perhaps a particular PROC, set of functions, or debugging methods. Since one of the ways we improve as SAS programmers is to expand our knowledge of the tools that are available to us, such presentations can be extremely useful.

3 However, merely having a wide repertoire of tools is not in itself enough to make someone a successful programmer, whether using SAS or other computer software. We need to be able to approach real-world problems in a systematic fashion, consider what tools are appropriate for solving them, and then design and develop a program that meets the specifications. (Needless to say, documentation, testing, and debugging are also critical parts of this process.) The main portion of this paper Starts by taking a look at two small data sets. We will then work through two examples of typical tasks we might need to perform using such data . For each example, we begin by stating the task, summarizing the output that is required, and listing some points that need to be considered when attacking the problem.

4 We then describe a basic approach for solving the problem. Finally, we present and discuss the SAS code needed to implement our solution. The code discussion includes brief descriptions of SAS tools and techniques used that may be unfamiliar to a significant number of SAS programmers. The first example is one that can be solved with a single SAS step, while the second requires multiple steps and considerably more code. I decided to focus on Episode data for a number of reasons. I've had considerable personal experience over the years dealing with health insurance and medical expenditures data . While there are many people who work in this area, I also realized that data involving periods of time are common in many other industries as well. Questions involving this type of data are also quite common on SAS-L.

5 For the uninitiated, these problems can often be difficult to tackle. SAS, however, does offer a wide range of tools that can be used to come up with creative and understandable solutions to such problems. Hopefully this paper, by systematically working through examples of such problems and their solutions, will enable you to more easily handle similar problems that you face in the future. A FEW QUICK NOTES ON SAS DATES This paper is certainly not intended to be a comprehensive discussion of all the tools that SAS has available for handling dates. You can get a good general introduction to this subject in the "Dates, Times, and Intervals" chapter of SAS Language Reference: Concepts. A few of the many excellent SAS conference papers discussing related issues and techniques are listed in the References section at the end of this paper.

6 However, I decided to include a very brief introduction here for the benefit of anyone who has not worked with dates in SAS before (and as a refresher for others). SAS does not actually have a built-in "date" type variable. Typically, however, a date value is stored in a single numeric SAS variable, and the many date-related functions, informats and formats assume that this is the case. The date value is an SUGI 29 Tutorials 2integer that represents the number of days since January 1, 1960. So, January 1, 1960 is represented by a value of 0. January 2, 1960 has a value of 1, while December 31, 1959 is represented as -1. This representation has several important consequences for solving the examples we will see later in this paper. First, it is easy to compare two dates, since the later date will have the larger numeric value.

7 Second, you can use date values in basic arithmetic operations (addition and subtraction). You can subtract one date value from another to get the number of days between them. You can also add or subtract a numeric value (number of days) from a date value, resulting in a new date value which is that number of days later or earlier than the initial date. You can use "constant" date values in your SAS programs, just as you can specify numeric or character constants (literals). You write a date constant by specifying the day of the month, followed by the 3-letter month abbreviation and the year. (This corresponds to the DATE. informat.) Enclose the result in quotes, immediately followed by a D to indicate that the string is a date constant rather than a string constant. For example, '1 JAN1960'D.

8 You may use single or double quotes, and the D and the month abbreviation are case-insensitive. If you include only two digits for the year (not recommended), the value of the YEARCUTOFF system option is used to determine the century. Always assign a format when you are working with variables holding date values, so the date will be displayed in a meaningful way. SAS offers a wide range of date formats (and if none of these meet your needs you can take advantage of date-time "directives" in PROC FORMAT to completely customize the display of your date values). Good general choices include YYMMDD10 and DATE9. Be sure to specify a length with the format, since many of the SAS defaults display 2-digit rather than 4-digit years. To save some space in this paper and the accompanying slides, I decided to use MMDDYY5 as the format for my date values.

9 This displays numeric month and day values separated by a slash ( 12/31 for December 31). The year is not shown, which obviously only makes sense if you are certain that all the dates you are working with are within the same year. THE data The data for our examples come from a fictitious study of the health insurance coverage of 8 people during 2002. Each of the 8 persons in the study has one record in the Demographics data set, whose variables are PersID, Gender, and Age. The second data set is InsCovData, which contains one record for each reported health insurance plan. The variables on InsCovData are PersID, PlanNum (a sequential identifier within PersID), PlanType (1=Medicare, 2=Medicaid, 3=Military, 4=Private), PlanStart, and PlanEnd. Persons with no health insurance coverage during 2002 (such as person #7 in our data ) do not have any records in InsCovData.

10 Plan records may overlap, and there may be periods that are not covered by any plan record. PersID PlanNumPlanTypePlanStartPlanEnd1 1 Medicare01/0112/312 1 Medicaid01/0101/312 2 Medicaid09/0111/303 1 Private01/0112/313 2 Medicare10/0112/314 1 Military07/0112/315 1 Private01/0110/315 2 Private10/0111/305 3 Private12/0112/316 1 Private01/0108/226 2 Medicaid11/0112/318 1 Military01/0102/158 2 Private02/0106/308 3 Medicaid09/0111/308 4 Private12/1512/318 5 Private12/1712/31 To keep the programs in the remainder of the paper from getting too complicated, we will specify that our data have the following characteristics: 1. For each record, PlanEnd is greater than or equal to PlanStart. 2. All values for PlanStart and PlanEnd are in 2002. 3. There are no missing values in the data .