Hi user22,
Although you have shown the sort of dataset you want to build, it is not terribly clear where you are starting from.
Does your existing dataset look something like this ?
In my example data (WORK.HAVE) above, an EnrollID exists in a single row and shows a StartDate and EndDate. In my data, the StartDate/EndDate are not nicely arranged to be the first and last dates of their corresponding months. The first row is a nice example, but by chance, other rows are just simply start and end dates that a customer may choose for enrollment in a plan, they are not nicely aligned to a month start/end date
.
I have assumed from your example that your FlagMonthly column simply an integer representing the month number for a given row.
If the above dataset is what you have and you would like something like the following, expanded version, please read on.
As you can see, the original four rows in WORK.HAVE have been expanded into WORK.WANT. Each block of rows represents an original row in WORK.HAVE and the number of rows displayed in WORK.WANT corresponds to a month in the range of dates given by StartDate and End Date.
In my example, I have made a rule that if an original record impinges at all on a given month, then there will be a row for that whole month. That is why EnrollID=1461 ends up with two rows covering the whole period 01Mar2011 to 30Apr2011, even though its original EndDate was 15Apr2011.This may not be suitable for your needs, so you will need to amend the code below.....or ask some more questions......😺
If you always wanted a record to be expanded to run through to 31Dec<yyyy> that is pretty easy to achieve, but more questions are going to be asked of you about your data, be prepared !
The whole code that I used is as follows:
********************************************************************************************************************************;
*Create some dummy data that hopefully represents the posters data that they have to start with. ;
********************************************************************************************************************************;
Data HAVE;
Infile DATALINES
;
Input EnrollID
EnrollFlag
StartDate Date9.
+1 EndDate Date9.
;
Format StartDate Date9.
EndDate Date9.
;
DATALINES;
1460 1 01Jan2011 31Dec2011
1461 1 01Mar2011 15Apr2011
1462 1 08Jan2011 28Feb2011
1463 1 23Apr2011 28Nov2011
;
Run;
********************************************************************************************************************************;
*Expand the original dataset. ;
*Create one row for each month contained within the range bound by StartDate and EndDate. ;
********************************************************************************************************************************;
Data WANT;
Set HAVE;
**************************************************************;
*How many month-end boundaries between the Start/End Date for ;
*this EnrollID ? ;
**************************************************************;
NumberOfMonths=IntCk("Month", StartDate, EndDate)+1;
**************************************************************;
*Loop over that number of months and generate a MonthStart and;
*a MonthEnd date for each month in the range. ;
*Output one new row into the WANT dataset for each month in ;
*the range for the EnrollID. Also determine the month-of-year ;
*number (Jan=1, Feb=2, Mar=3) for each generated row. ;
**************************************************************;
Do Count=1 To NumberOfMonths;
MonthStart=IntNx("Month", StartDate, Count-1, "B");
MonthEnd =IntNx("Month", StartDate, Count-1, "E");
FlagMonthly=Month(MonthStart);
Output;
End;
**************************************************************;
*Make our date variables easy to read. ;
**************************************************************;
Format MonthStart Date9.
MonthEnd Date9.
;
**************************************************************;
*Drop the columns we no longer require to keep things tidy. ;
**************************************************************;
Drop Count
NumberOfMonths
StartDate
EndDate
;
Run;
I hope that this helps, and if not, come back to this post with a bit more detail as to what you existing data looks like.
Cheers,
Downunder Dave.
... View more
![SASGF 2020 [Virtual] Attendee](/html/@4229D83652827A3CCCDB8CAD5D23E8D2/badge_icons/badge_sasgf20_attendee.png "SASGF 2020 [Virtual] Attendee"){kind=icon}
