Help using Base SAS procedures

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

Reply
New Contributor
Posts: 4

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


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.

Super User
Posts: 17,775

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

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.

New Contributor
Posts: 4

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

Input data
Member Nrjan/11feb/11mrt/11apr/11mei/11jun/11jul/11aug/11sep/11okt/11nov/11dec/11jan/12feb/12mrt/12apr/12mei/12jun/12jul/12aug/12sep/12okt/12nov/12dec/12jan/13feb/13mrt/13apr/13mei/13jun/13jul/13aug/13sep/13okt/13nov/13dec/13jan/14feb/14mrt/14apr/14mei/14jun/14jul/14aug/14sep/14
1111111111000000000000000000000000000000000000
2111110011111111111111111111111111111111111111
3111111100111111110000000000000000111111111111
MemberBegin dateEnd date1st suspension start1st suspension end2nd suspension start2nd suspension end
11/01/20111/10/2011NANANANA
21/01/20111/10/20141/06/20111/08/2011NANA
31/01/20111/10/20141/08/20111/10/20111/06/20121/10/2013
New Contributor
Posts: 4

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

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

Super User
Posts: 9,671

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

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

Respected Advisor
Posts: 3,887

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

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=_Smiley Happy;

  by Member;

  var flag;

  id date;

run;

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

run;

Respected Advisor
Posts: 3,124

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

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;

;

New Contributor
Posts: 4

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

Thanks guys.

Ask a Question
Discussion stats
  • 7 replies
  • 294 views
  • 1 like
  • 5 in conversation