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
... View more