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.
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.
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 |
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
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
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;
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;
;
Thanks guys.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.