How do I assign a 1 or 0 to months when a person is eligible?

[cmac3]

I have a begin period, say 1/1/2011

I have an end period, say 1/10/2014

I want to write a code that outputs a 1 for every month this person is eligible and a 0 if the person ceased eligibility.

Within the eligibility period it is possible that a person could have been suspended for a number of months. How do I write a code that gives the suspension period preference over the eligibility?

Example: Person is eligible from 1/6/2011, suspended from 1/10/2011 until 1/12/2011. The person resumed coverage for two more months and then terminated coverage. Each binary output should be allocated to a new column with the Month/Year heading. The output from 1/1/2011 for this example would look like:

0 0 0 0 0 1 1 1 1 0 0 0 1 1 0 0 0 0 0 ...etc

Thanks in advance.

[Reeza]

What does your input data look in terms of a data set?

Will your periods align for all people in the data set, if not, how do you want that represented?

Sample input/output is good way to show it.

Either way you'll use the intnx function to increment your month and possibly an array to store your 1/0.

[cmac3]

Input data
Member Nr	jan/11	feb/11	mrt/11	apr/11	mei/11	jun/11	jul/11	aug/11	sep/11	okt/11	nov/11	dec/11	jan/12	feb/12	mrt/12	apr/12	mei/12	jun/12	jul/12	aug/12	sep/12	okt/12	nov/12	dec/12	jan/13	feb/13	mrt/13	apr/13	mei/13	jun/13	jul/13	aug/13	sep/13	okt/13	nov/13	dec/13	jan/14	feb/14	mrt/14	apr/14	mei/14	jun/14	jul/14	aug/14	sep/14
1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2	1	1	1	1	1	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
3	1	1	1	1	1	1	1	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1
Member	Begin date	End date	1st suspension start	1st suspension end	2nd suspension start	2nd suspension end
1	1/01/2011	1/10/2011	NA	NA	NA	NA
2	1/01/2011	1/10/2014	1/06/2011	1/08/2011	NA	NA
3	1/01/2011	1/10/2014	1/08/2011	1/10/2011	1/06/2012	1/10/2013

[cmac3]

The first exhibit is how I want the output to look. The lower exhibit is the data I have to work with. There can be as many as three suspension periods.

Thanks in advance

[Ksharp]

How about :

data have;
input Member     (Begin_date     End_date  start1 end1     start2 end2) (: ddmmyy10.);
format      Begin_date      End_date  start1 end1     start2 end2 ddmmyy10.;
cards;
1     1/01/2011     1/10/2011     .     .     .     .
2     1/01/2011     1/10/2014     1/06/2011     1/08/2011     .     .
3     1/01/2011     1/10/2014     1/08/2011     1/10/2011     1/06/2012     1/10/2013
;
run;
data temp;
 set have;
 retain flag 1;
 do i=0 to intck('month',Begin_date,End_date);
  date=intnx('month',Begin_date,i);output;
 end;
 format date ddmmyy10.;
 drop i Begin_date     End_date;
run;
data temp1;
 set temp;
 array x{*} start1-start2;
 array y{*} end1-end2;
 do i=1 to dim(x);
  if not missing(x{i}) and not missing(y{i}) then do;
    do j=0 to intck('month',x{i},y{i});
      if intnx('month',x{i},j) eq date then flag=0;
    end;
  end;
 end;
 format date monyy.;
 keep  Member flag date;
run;
proc transpose data=temp1 out=temp2(drop=_:);
 by Member ;
 var flag;
 id date;
run;
proc stdize data=temp2 out=want missing=0 reponly;run;

Xia Keshan

Message was edited by: xia keshan

[Patrick]

Only as a rather small variation to what posted.

data have;

  input Member (Begin_date End_date start1 end1 start2 end2) (: ddmmyy10.);

  format Begin_date End_date start1 end1 start2 end2 ddmmyy10.;

  cards;

1 1/01/2011 1/10/2011 . . . .

2 1/01/2011 1/10/2014 1/06/2011 1/08/2011 . .

3 1/01/2011 1/10/2014 1/08/2011 1/10/2011 1/06/2012 1/10/2013

;

run;

data inter(keep=Member flag date);

  format date monyy.;

  set have;

  array suspend {2,2} start1-start2 end1-end2;

  date=Begin_date;

  do until (date>end_date);

    flag=1;

    if date=end_date then flag=0;

    else

      do _i=1 to hbound(suspend,2);

        if suspend[1,_i] <= date < suspend[2,_i] then

          do;

            flag=0;

            leave;

          end;

      end;

    output;

    date=intnx('month',date,1,'b');

  end;

run;

proc transpose data=inter out=want(drop=_:);

  by Member;

  var flag;

  id date;

run;

proc stdize data=temp2 out=want missing=0 reponly;

run;

[Haikuo]

Another option:

data have;

     input Member     (Begin_date     End_date start1 end1     start2 end2) (: ddmmyy10.);

     format      Begin_date      End_date start1 end1     start2 end2 ddmmyy10.;

     cards;

1     1/01/2011     1/10/2011     . .     .     .

2     1/01/2011     1/10/2014     1/06/2011     1/08/2011     . .

3     1/01/2011     1/10/2014     1/08/2011     1/10/2011     1/06/2012     1/10/2013

4     1/03/2011     1/10/2013     1/06/2012     1/08/2013     . .

;

run;

proc sql noprint;

     select  min(begin_date), max(end_date) into :m1,:m2 trimmed from have;

quit;

data _null_;

     length var $ 1000;

     do dim=0 by 1 to intck('month',&m1, &m2);

           var=catx(' ', var, put(intnx('month', &m1, dim, 'b'), monyy7.));

     end;

     call symputx('array', var);

     call symputx('dim',dim);

run;

data want;

     set have;

     array mon(&dim) &array;

     /*reset all month to 0*/

     do i=1 to dim(mon);

           mon(i)=0;

     end;

     /*set month from begin to end to 1*/

     if not missing (Begin_date + End_date) then

           do i=1 to intck('month', Begin_date,  End_date)+1;

                mon(intck('month',   &m1,Begin_date)+i)=1;

           end;

     /*set month to 0, if month falls in between suspension #1*/

     if not missing (start1+End1) then

           do i=1 to intck('month', start1,  End1)+1;

                mon(intck('month',  &m1,start1)+i)=0;

           end;

     /*set month to 0, if month falls in between suspension #2*/

     if not missing (start2+End2) then

           do i=1  to intck('month', start2,  End2)+1;

                mon(intck('month',   &m1,start2)+i)=0;

           end;

     drop i;

run;

;

[cmac3]

Thanks guys.