```Date: Thu, 22 Jul 2010 10:03:27 -0400 Reply-To: Dave Brewer Sender: "SAS(r) Discussion" From: Dave Brewer Subject: Re: How to code to calculate continuous 12 month period Comments: To: "Sigurd W. Hermansen" Sig, I'll be curious to see your SQL solution, if you can come up with one. Thanks much for looking into this. Dave On Thu, 22 Jul 2010 09:53:00 -0400, Sigurd Hermansen wrote: >Dave: >My head is spinning as well. Ian Whitlock has advised me discretely that the program with the nseq parameter set to 12 doesn't work as required for the extended test data I posted. The current program admits sequences of the prescribed number but with gaps. Ian suggests that a solution in SQL for this class of problem may not be possible. As time allows, I plan to review the problem again and see if I can determine whether a SQL solution can exist, and, if so, how to write it. >S > >-----Original Message----- >From: Dave Brewer [mailto:david.brewer@UC.EDU] >Sent: Thursday, July 22, 2010 6:05 AM >To: SAS-L@LISTSERV.UGA.EDU; Sigurd Hermansen >Subject: Re: How to code to calculate continuous 12 month period > >Wow...my head is still spinning on your approach to my problem. It is most >definitely a unique approach. Thanks for sharing. > >Thanks again. >Dave > > >On Wed, 21 Jul 2010 14:17:44 -0400, Sigurd Hermansen >wrote: > >>Dave: >>One might suspect that a solution based on predicate logic instead of >sequential procedure requires more thought and initial effort, although in >my case slower and more easily distracted thought processes could also >explain this delayed result: >> >>/* test data */ >>data one; >> input id mon :anydtdte.; >> format mon mmddyys10.; >>cards; >>1 01/01/2009 >>1 02/01/2009 >>1 03/01/2009 >>1 06/01/2009 >>1 07/01/2009 >>1 08/01/2009 >>1 09/01/2009 >>1 10/01/2009 >>1 11/01/2009 >>1 12/01/2009 >>1 04/01/2010 >>1 06/01/2010 >>1 07/01/2010 >>1 08/01/2010 >>2 05/01/2009 >>2 08/01/2009 >>2 09/01/2009 >>2 10/01/2009 >>2 11/01/2009 >>2 12/01/2009 >>2 01/01/2010 >>2 02/01/2010 >>2 03/01/2010 >>2 04/01/2010 >>2 05/01/2010 >>2 06/01/2010 >>2 07/01/2010 >>; >>run; >>%let nseq=12; >>proc sql; >> create view vwGE12 as >> select *,year(mon) * 100 + month(mon) as yearMon >> from one group by id having count(*) >= &nseq >> ; >> create view vwGE12groups as >> select distinct r1.id as id1,r1.yearMon as yearMon1,r2.id as >id2,r2.yearMon as yearMon2 >> from vwGE12 as r1 inner join vwGE12 as r2 >> on r1.id=r2.id and NOT (r1.mon=r2.mon) and abs(r1.yearMon - >r2.yearMon) = 1 >> ; >> create table GE12groups as >> select distinct id1 as id,yearMon1 as yearMon >> from vwGE12groups group by ID having count(*) >= &nseq >> order by ID,yearMon >> ; >>quit; >> >> >>The solution could likely be more concise. I've divided the solution into >three parts to make it easier to follow. The first imposes the condition >that an id must have nseq tuples (subsetting for performance). The second >requires sequential months, and the third reimposes the nseq tuples >condition. >> >>This solution doesn't require sorting or other preconditions on data >sources, extends easily to other database programming platforms, and can >be adapted easily to similar problems. It will handle different sequence >counts in a natural way. >> >>While it may seem that I am spending time on these database programming >problems solely to challenge the perceived limits of set logic, I am >actually trying to catch up with some exceptionally talented practitioners >of the art of functional sequence (structured procedural) programming. I >have the perhaps dubious idea that set-logic programming of databases >should offer advantages over procedural programming much as multiplication >offers advantages over successive addition. Hasn't worked out that way so >far. Nonetheless, I still have hopes for the next generation of database >programmers. >>S >> >>-----Original Message----- >>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dave >Brewer >>Sent: Wednesday, July 21, 2010 10:00 AM >>To: SAS-L@LISTSERV.UGA.EDU >>Subject: Re: How to code to calculate continuous 12 month period >> >>Hi Nat, >> >>Thanks so much for your help...it is greatly appreciated. >> >>Dave >> >>On Wed, 21 Jul 2010 09:52:20 -0400, Nat Wooding >>wrote: >> >>>Dave >>> >>>I switched a couple lines so that the Intck function does not come into >>play >>>when LastMo is missing. >>> >>>Nat >>> >>>Data Selected; >>> set ONE; >>> by PatientID; >>> >>> Format Begin_Date LastMO mmddyy10.; >>> If first.patientid then Continuous_Months = 1 ; * no longer a >>> loop; >>> >>> LastMo = Lag( Begin_Date ); >>> >>>* the following order of logic keeps Intck from producing a missing >value; >>> If lastmo = . then Continuous_Months = 1; else >>> if Intck( 'Month' , LastMO , Begin_Date ) = 1 and first.PatientId >>> ne 1 then Continuous_Months +1; >>> >>> >>> if last.PatientID and Continuous_Months ge &NumMonths then >>output >>>; >>> Drop LastMo; >>> >>> run; >>> >>> >>>-----Original Message----- >>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dave >>>Brewer >>>Sent: Wednesday, July 21, 2010 9:37 AM >>>To: SAS-L@LISTSERV.UGA.EDU >>>Subject: Re: How to code to calculate continuous 12 month period >>> >>>Hi Nat, >>> >>>Thanks for your revised code; it produces the correct record. >>> >>>However, I still get a NOTE in the Log about "missing values were >>>calculated...". How do I get rid of that annoying message? >>> >>>Thanks again. >>>Dave >>> >>>On Wed, 21 Jul 2010 09:14:02 -0400, Nat Wooding >>>wrote: >>> >>>>Dave >>>> >>>>Dave >>>> >>>>I changed my code at the marked places. >>>> >>>>Nat >>>>(Toby and I are even worse off-line) >>>> >>>> >>>>%let NumMonths = 24; >>>>Data Selected; >>>> set ONE; >>>> by PatientID; >>>> >>>> Format Begin_Date LastMO mmddyy10.; >>>> If first.patientid then Continuous_Months = 1 ; * no longer a >>>>loop; >>>> >>>> LastMo = Lag( Begin_Date ); >>>> * used for testing Delta = Intck( 'Month' , LastMO , >>>>Begin_Date ); >>>> * I changed the following line; >>>> if Intck( 'Month' , LastMO , Begin_Date ) = 1 and >>>first.PatientId >>>>ne 1 then >>>> Continuous_Months +1; >>>> else Continuous_Months = 1; >>>> >>>> if last.PatientID and Continuous_Months ge &NumMonths then >>>output >>>>; >>>> Drop LastMo; >>>> >>>> run; >>>> >>>>-----Original Message----- >>>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >Dave >>>>Brewer >>>>Sent: Wednesday, July 21, 2010 8:47 AM >>>>To: SAS-L@LISTSERV.UGA.EDU >>>>Subject: Re: How to code to calculate continuous 12 month period >>>> >>>>Nat, >>>> >>>>Thanks much for your suggestion, but I have a problem with your and >>>>Chang's code when checking for 24 continuous months. >>>> >>>>Besides the NOTE in the log, I don't get any records selected; ID=2 >>should >>>>be selected. >>>> >>>>What do I need to do to get your code to select the proper records and >>>>eliminate the missing values message? >>>> >>>>Thanks much (and I enjoy your kibitzing with Toby!). >>>>Dave >>>> >>>> >>>>NOTE: Missing values were generated as a result of performing an >>operation >>>>on missing values. >>>> >>>>My code: >>>>data one; >>>> input id mon :anydtdte.; >>>> format mon mmddyys10.; >>>>cards; >>>>1 7/1/2008 >>>>1 8/1/2008 >>>>1 9/1/2008 >>>>1 10/1/2008 >>>>1 11/1/2008 >>>>1 12/1/2008 >>>>1 1/1/2009 >>>>1 2/1/2009 >>>>1 3/1/2009 >>>>1 4/1/2009 >>>>1 5/1/2009 >>>>1 6/1/2009 >>>>1 7/1/2009 >>>>1 8/1/2009 >>>>1 9/1/2009 >>>>1 10/1/2009 >>>>1 11/1/2009 >>>>1 12/1/2009 >>>>1 1/1/2010 >>>>1 2/1/2010 >>>>1 3/1/2010 >>>>1 4/1/2010 >>>>2 7/1/2008 >>>>2 8/1/2008 >>>>2 9/1/2008 >>>>2 10/1/2008 >>>>2 11/1/2008 >>>>2 12/1/2008 >>>>2 1/1/2009 >>>>2 2/1/2009 >>>>2 3/1/2009 >>>>2 4/1/2009 >>>>2 5/1/2009 >>>>2 6/1/2009 >>>>2 7/1/2009 >>>>2 8/1/2009 >>>>2 9/1/2009 >>>>2 10/1/2009 >>>>2 11/1/2009 >>>>2 12/1/2009 >>>>2 1/1/2010 >>>>2 2/1/2010 >>>>2 3/1/2010 >>>>2 4/1/2010 >>>>2 5/1/2010 >>>>2 6/1/2010 >>>>; >>>>run; >>>> >>>> >>>>On Tue, 20 Jul 2010 17:15:13 -0400, Nat Wooding >>>>wrote: >>>> >>>>>Dave >>>>> >>>>>Here is a variation of Chang's code. >>>>> >>>>>Nat Wooding >>>>> >>>>>%let NumMonths = 12; ** this is the number of months in the period that >>>>you >>>>>are checking for; >>>>> >>>>> >>>>>Data Dave; >>>>>Informat PatientID 8. Begin_Date mmddyy10.; >>>>>input PatientID Begin_Date ; >>>>>cards; >>>>>1 01/01/2009 >>>>>1 02/01/2009 >>>>>1 03/01/2009 >>>>>1 06/01/2009 >>>>>1 07/01/2009 >>>>>1 08/01/2009 >>>>>1 09/01/2009 >>>>>1 10/01/2009 >>>>>1 11/01/2009 >>>>>1 12/01/2009 >>>>>1 04/01/2010 >>>>>2 05/01/2009 >>>>>2 08/01/2009 >>>>>2 09/01/2009 >>>>>2 10/01/2009 >>>>>2 11/01/2009 >>>>>2 12/01/2009 >>>>>2 01/01/2010 >>>>>2 02/01/2010 >>>>>2 03/01/2010 >>>>>2 04/01/2010 >>>>>2 05/01/2010 >>>>>2 06/01/2010 >>>>>2 07/01/2010 >>>>>; >>>>>Data Selected; >>>>> set dave; >>>>> by PatientID; >>>>> Format Begin_Date LastMO mmddyy10.; >>>>> If first.patientid then do; >>>>> Continuous_Months = 1 ; >>>>> return; >>>>> end; >>>>> LastMo = Lag( Begin_Date ); >>>>> if Intck( 'Month' , LastMO , Begin_Date ) = 1 then >>>>>Continuous_Months +1; else Continuous_Months = 1; >>>>> >>>>> if last.PatientID and Continuous_Months ge &NumMonths then >>>>output ; >>>>> Drop LastMo; >>>>> >>>>>run; >>>>> >>>>> >>>>>-----Original Message----- >>>>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >>Dave >>>>>Brewer >>>>>Sent: Tuesday, July 20, 2010 4:49 PM >>>>>To: SAS-L@LISTSERV.UGA.EDU >>>>>Subject: How to code to calculate continuous 12 month period >>>>> >>>>>Hi All, >>>>> >>>>>I have been trying to come up with code to calculate if a patient has >>>been >>>>>enrolled in Medicaid for 12 continuous months, but to no avail. >>>>> >>>>>The data consists of a begin date (SAS date) and patient id. The end >>date >>>>>will always be at the end of that month, so it is not needed. >>>>> >>>>>Data: >>>>>Patient ID Begin_Date >>>>>1 01/01/2009 >>>>>1 02/01/2009 >>>>>1 03/01/2009 >>>>>1 06/01/2009 >>>>>1 07/01/2009 >>>>>1 08/01/2009 >>>>>1 09/01/2009 >>>>>1 10/01/2009 >>>>>1 11/01/2009 >>>>>1 12/01/2009 >>>>>1 04/01/2010 >>>>>2 05/01/2009 >>>>>2 08/01/2009 >>>>>2 09/01/2009 >>>>>2 10/01/2009 >>>>>2 11/01/2009 >>>>>2 12/01/2009 >>>>>2 01/01/2010 >>>>>2 02/01/2010 >>>>>2 03/01/2010 >>>>>2 04/01/2010 >>>>>2 05/01/2010 >>>>>2 06/01/2010 >>>>>2 07/01/2010 >>>>> >>>>>ID 1 would not be selected but ID 2 would. The continuous period does >>not >>>>>have to be within a calendar year and I can have as few as 5 records >and >>>>>as many as 36 records, but the max number of records could change (up >or >>>>>down). >>>>> >>>>>I would like the code to be dynamic; in other words, I might need to >>>check >>>>>for 24 continuous months the next time. >>>>> >>>>>Thanks for your help. >>>>>Dave ```

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