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.
Hi User22.
I think this next version may be closer to where you need to be, I hope so anyway.
Please have a read through the code and the associated comments and see whether the results you get from this version work for you or not.
As is often the way with these "programming by remote control" posts, I have slowed this down and probably used more steps and statements than someone else might do. Others may criticise in terms of finesse, but I use this way to, hopefully, make the method that I am using, clearer. If it works, and you want to spend some time making it shorter, knock yourself out.
I have added an optional statement in the final DATA step to set those rows with a NULL/MISSING value for EnrollFlag to appear instead as -99999. I have done this to make is very obvious that this data may need attention. You can comment out this IF/THEN statement if you wish or choose a different value in place of -99999.
I have also added a single original record for EnrollID=1236 to simulate what happens when an EnrollID only appears in your original data in one row. I may have got the idea about your original data wrong, so beware.
There is a limitation in this version of the code that may not worry you at all or at this point. That limitation is that it assumes all of your data will be for the calendar year 2011. Should/when your needs expand beyond data for a single year, all is not lost, just a little more complexity is added to the code which I do not think it worth muddying the waters at this point.
********************************************************************************************************************************;
*Create some dummy data. ;
********************************************************************************************************************************;
Data WORK.HAVE;
Infile DATALINES
;
Input EnrollID
EnrollFlag
StartDate Date9.
+1 EndDate Date9.
;
Format StartDate Date9.
EndDate Date9.
;
DATALINES;
1234 1 01Mar2011 31Mar2011
1234 1 01Apr2011 30Apr2011
1234 1 01May2011 31May2011
1234 1 01Jun2011 30Jun2011
1234 1 01Jul2011 31Jul2011
1234 1 01Aug2011 31Aug2011
1234 1 01Sep2011 30Sep2011
1234 1 01Oct2011 31Oct2011
1234 1 01Nov2011 30Nov2011
1234 0 01Dec2011 31Dec2011
1235 1 01Jan2011 31Jan2011
1235 1 01Feb2011 28Feb2011
1235 0 01Mar2011 31Mar2011
1235 1 01Apr2011 30Apr2011
1235 1 01May2011 31May2011
1235 1 01Jun2011 30Jun2011
1235 1 01Jul2011 31Jul2011
1235 . . .
1235 1 01Sep2011 30Sep2011
1235 1 01Oct2011 31Oct2011
1235 1 01Nov2011 30Nov2011
1235 1 01Dec2011 31Dec2011
1236 1 01Apr2011 30Apr2011
;
Run;
********************************************************************************************************************************;
*Create a simple look-up list of distinct EnrollIDs in the current data. ;
********************************************************************************************************************************;
Proc SQL;
Create Table EnrollIDs As
Select EnrollID,
Count(*) As NumberOfRows Format=Comma12. Label="Original Number of Rows fir EnrollID"
From WORK.HAVE
Group By EnrollID
;
Quit;
********************************************************************************************************************************;
*Create an empty frame of 12 months, for each known EnrollID, assuming we are only interested in the year 2011. ;
********************************************************************************************************************************;
Data WORK.Empty_Frame;
Set EnrollIDs;
**************************************************************;
*ASSUMPTION: This data only encompasses the year 2011. ;
**************************************************************;
InitialDate="01Jan2011"d;
Do Count=1 To 12;
StartDate=IntNx("Month", InitialDate, Count-1, "B");
EndDate =IntNx("Month", InitialDate, Count-1, "E");
FlagMonthly=Month(StartDate);
Output;
End;
Format StartDate Date9.
EndDate Date9.
;
Drop Count
InitialDate
;
Run;
********************************************************************************************************************************;
*Sort our two datasets ready for merging/joining. ;
********************************************************************************************************************************;
Proc Sort Data=WORK.HAVE;
By EnrollID
StartDate
;
Run;
Proc Sort Data=WORK.Empty_Frame;
By EnrollID
StartDate
;
Run;
********************************************************************************************************************************;
*Use the WORK.Empty_Frame dataset as our base and merge/join on any corresponding rows from WORK.HAVE. ;
********************************************************************************************************************************;
Data All_Together;
Informat EnrollID EnrollFlag;
Merge WORK.Empty_Frame(In=Empty_Frame)
WORK.HAVE(In=HAVE)
;
By EnrollID
StartDate
;
**********************************************************************************;
*Continue the pass of the DATA step if a row at least appears in WORK.EMPTY_FRAME ;
**********************************************************************************;
If Empty_Frame;
**************************************************************;
*OPTIONAL BIT: ;
*If the current row is one of our filler-rows or it started as;
*as an empty row in the source dataset, make it obvious. ;
**************************************************************;
If EnrollFlag=. Then EnrollFlag=-99999;
Keep EnrollID
EnrollFlag
StartDate
EndDate
FlagMonthly
;
Run;
********************************************************************************************************************************;
*Make sure that the rows are in a readable order again....they ought to be, but........ ;
********************************************************************************************************************************;
Proc Sort Data=All_Together;
By EnrollID
StartDate
;
Run;Anyhow, good luck with this latest version, I hope that it moves you on from the previous version.
If you post data or code in future, you should try to use the little SAS Code icon on the tool bar on the edit window, highlighted in red below. Using that tool makes the code you paste in use a better and more readable type of formatting:
Keep at it, you will get there !
Cheers,
Downunder Dave
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
845 views
-
0 likes
-
-
