BookmarkSubscribeRSS Feed
cmac3
Calcite | Level 5


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.

7 REPLIES 7
Reeza
Super User

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
Calcite | Level 5
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
cmac3
Calcite | Level 5

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
Super User

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
Opal | Level 21

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
Onyx | Level 15

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;

;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1050 views
  • 1 like
  • 5 in conversation