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

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

cmac3 — Fri, 28 Nov 2014 09:15:18 GMT

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.

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

Reeza — Fri, 28 Nov 2014 10:44:40 GMT

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.

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

cmac3 — Fri, 28 Nov 2014 11:09:44 GMT

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

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

cmac3 — Fri, 28 Nov 2014 11:11:43 GMT

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

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

Ksharp — Fri, 28 Nov 2014 14:40:07 GMT

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

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

Haikuo — Fri, 28 Nov 2014 18:03:07 GMT

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;

;

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

Patrick — Sat, 29 Nov 2014 00:57:01 GMT

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;

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;

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

cmac3 — Tue, 02 Dec 2014 15:41:30 GMT

Thanks guys.