```Date: Mon, 14 May 2001 11:26:18 -0400 Reply-To: Howard Schreier Sender: "SAS(r) Discussion" From: Howard Schreier Subject: Re: Eligibility, Dates and Arrays Here's one way (tested). Load the specific calendar year into a macrovariable: %let year = 2000; Load test data (for test purposes, I've added an observation for the case of no eligible months): data example; input id m1-m12 Eligible_Months; cards; 101 1 1 1 1 1 1 1 1 1 1 1 1 12 102 1 1 1 1 1 1 1 1 0 0 0 0 8 103 0 0 0 0 1 1 1 1 1 1 0 0 6 104 0 0 0 1 1 1 1 1 1 1 1 1 9 105 1 1 1 0 0 1 1 1 1 1 1 1 10 106 0 0 1 1 1 1 0 0 1 1 1 0 7 107 0 0 0 0 0 0 0 0 0 0 0 0 0 ; As suggested, use an ARRAY to do the counting. The trick is to add months zero and thirteen as "bookends": data intervals; set example; retain m0 m13 0; keep id _name_ date; array flag(0:13) m0-m13; intervalnum = 0; do month = 1 to 12; if flag(month-1)=0 and flag(month)=1 then do; intervalnum + 1; _name_ = 'start' || put(intervalnum,1.); date = mdy(month,1,&YEAR); output; end; if flag(month+1)=0 and flag(month)=1 then do; _name_ = 'end' || put(intervalnum,1.); date = intnx('month',mdy(month,1,&YEAR),0,'end'); output; end; end; run; Now let PROC TRANPOSE create the needed new variables (this approach avoids a lot of messy details involving the maximum number of eligibility intervals which might occur): proc transpose data=intervals out=tomerge(drop=_name_); by id; var date; run; Combine with the original data: data done; merge example tomerge; by id; format start: end: date9.; run; On Mon, 14 May 2001 09:18:31 -0500, Foy, Thomas M. wrote: >SAS Users and Masters: > >I need help/assistance with a data set I am working with. I have a health >care eligibility file that I need to read through and determine start dates >and end dates. The file contains data for one calendar year, each persons' >eligibility for coverage is denoted by a flag in a variable for each month, >and their total eligible months is in a another variable. What I need to >do is to read through each record, count the flags, and determine a start >date and end date. The file looks like the following: > >ID# M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 >M11 M12 Eligible_Months >101 1 1 1 1 1 1 1 1 >1 1 1 1 12 >102 1 1 1 1 1 1 1 1 >0 0 0 0 8 >103 0 0 0 0 1 1 1 1 >1 1 0 0 6 >104 0 0 0 1 1 1 1 1 >1 1 1 1 9 >105 1 1 1 0 0 1 1 1 >1 1 1 1 10 >106 0 0 1 1 1 1 0 0 >1 1 1 0 7 > >Where M1 is January, M2 is February, ...M12 is December, and Eligible_Months >is the total number of months that person was eligible for coverage that >year. If Eligible_Months is 12 then it's not a problem, the start date is >01Jan-year and the end date is 31DEC-year. It's when the person has less >than 12 months of eligibility that I am having trouble with. ID#'s 105 an >106 are a particular problem with multiple start and end dates. In those >cases I need to string them together on one line such as; start_1 end_1 >start_2 end_2 .... With the total eligible months at the end. > >This problem is screaming for an array. But I'm not entirely sure how to go >about reading the elements until I get to the first non-zero element, and >then stopping at the last non-zero element. When I determine what the start >and end months are I will need to assign an actual date to them, i.e. >start_1 = 01Jan1999, end_1 = 31Aug1999. > >Any assistance with this will be greatly appreciated. > >Thank you, > >Thomas M. Foy >Sr. Research Programmer/Analyst >Park Nicollet Institute >Health Research Center >Minneapolis, Minnesota 55416 ```

Back to: Top of message | Previous page | Main SAS-L page