Date: Wed, 21 Jul 2010 14:17:44 -0400
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: How to code to calculate continuous 12 month period
In-Reply-To: <201007211359.o6LAmIel013674@willow.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"
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 <nathani@VERIZON.NET>
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 <nathani@VERIZON.NET>
>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 <nathani@VERIZON.NET>
>>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
| 
