topic Re: insurance claims: evaluating period of enrollment prior to diagnosis date in SAS Programming

insurance claims: evaluating period of enrollment prior to diagnosis date

jwsph — Sat, 16 Apr 2011 17:14:52 GMT

hello - I'm stuck on something I was hoping to get some help with.

I am working with insurance claims data, in this case, the enrollment file. I have 48 months worth of data, and am trying to do two things:

1. for each id, determine whether the person had a continuous 12 or more month period of enrollment at any time during the 48 months, and if so, store the first and last months of enrollment (there may be gaps in enrollment, so a given id may have more than one 12+ month continuous period).

2. for each id, evaluate the dx_month, and see if the person had 12 months of continuous enrollment for the 12 months prior. So for a person diagnosed in month 13, was he continuously enrolled between months 1 and 12.

• dx_month = month during the 4-yr period that the person was diagnosed with hypertension
• e1-e26 = binary variables indicating enrollment during each month (1=yes). My dataset actually has e1-e48 but I didn't include all here.

data temp;
input id dx_month e1 e2 e3 e4 e5 e6 e7 e8 e9 e10 e11 e12 e13 e14 e15 e16
e17 e18 e19 e20 e21 e22 e23 e24 e25 e26;
cards;
101 13 1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 1 1 1 1 1 1 1 1 1
102 20 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
103 15 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0
104 14 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0
105 22 1 1 1 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 0
106 11 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0
107 20 1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 1 1 1 1 1 1 1 1 1

Any help would be greatly appreciated. I've tried lags, macros, etc. but am not getting it to work.. 😞 Thank you.

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

sbb — Sat, 16 Apr 2011 17:45:47 GMT

Using an ARRAY for your Enn variables, have a DO/END loop that tests the prior 12 variable-values in the array to check for a 1 -- if any are 0, then you have failed to match a qualification.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

data step array site:sas.com

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

jwsph — Sun, 17 Apr 2011 14:49:53 GMT

thank you for the reply Scott. l used arrays to set the e1-e48 indicators, but do not have much experience with syntax for them...specifically, I am getting an 'array subscript out of range' error message when trying to evaluate the specific values (i.e., if arrayname{i} = 0 then DO...). I wrote my 'DO i=' statement as

do i = (dx_month-12) to (dx_month-1);

Can you (or anyone else), help further with this?

thank you..

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

sbb — Sun, 17 Apr 2011 19:09:27 GMT

Did you by opportunity review the SAS support website technical document references - those revealed with the Google advanced search argument provided?

Scott Barry
SBBWorks, Inc.

http://www2.sas.com/proceedings/sugi30/242-30.pdf

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

jwsph — Sun, 17 Apr 2011 21:22:31 GMT

yes

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

DBailey — Sun, 17 Apr 2011 22:33:09 GMT

How is your enrollment data structured? I would recommend restructuring it to a vertical format like:
id month_start month_end
101 1 12
101 15 48
102 1 48
103 9 22
etc

proc sql;
create table work.dx_history as
select t1.id, t1.dx_month, intck('month',t2.month_start,t1.dx_month) as Months_Continuous_Enrollment_Prior_to_dx
from work.temp t1 inner join work.enrollment_vertical t2
on t1.id=t2.id and t2.dx_month between t2.month_start and t2.month_end;
quit;

Not quite where you were going...but it shouldn't be too hard to restructure your enrollment data.

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

Ksharp — Mon, 18 Apr 2011 10:43:21 GMT

I give you answer code for Question One.
I will give code for Question Two tomorrow.I have to leave now;

I make a variable e_end to flag the end for lines;
[pre]
data temp;
input id dx_month e1 e2 e3 e4 e5 e6 e7 e8 e9 e10 e11 e12 e13 e14 e15 e16
e17 e18 e19 e20 e21 e22 e23 e24 e25 e26 e_end;
cards;
101 13 1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 1 1 1 1 1 1 1 1 1 0
102 20 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 0
103 15 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0
104 14 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0
105 22 1 1 1 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 0 0
106 11 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0
107 20 1 1 1 1 1 1 1 1 1 1 1 1 0 0 1 1 1 1 1 1 1 1 1 1 1 1 0
;
run;
data QuestionOne;
set temp;
array e{*} e:;
do i=1 to dim(e)-12;
if e{i} = 1 then do;
j=i+1;count=0;count+1;
do while(e{j}=1);
j+1;count+1;
end;
if count ge 12 then do;
range=catx('-',vname(e{i}),vname(e{j-1}));
output;
end;
end;
end;
keep id range;
run;

[/pre]

Ksharp

Message was edited by: Ksharp

Message was edited by: Ksharp

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

art297 — Mon, 18 Apr 2011 13:01:59 GMT

The following attempts to correct for one oversight in ksharp's suggestion and also offers a possible suggestion for your other question:

data QuestionOne;
set temp;
array e{*} e:;
do i=1 to dim(e)-12;
if e{i} = 1 then do;
j=i+1;count=0;count+1;
do while(j lt dim(e) and e{j}=1);
j+1;count+1;
end;
if count ge 12 then do;
range=catx('-',vname(e{i}),vname(e{j-1}));
output;
end;
end;
end;
keep id range;
run;

%let nmonths=26;
data QuestionTwo (drop=i);
set temp;
array enroll(*) e1-e&nmonths.;
if dx_month le 12 then months=0;
else do;
do i=1 to &nmonths.;
if not(i ge dx_month-12 and i le (dx_month-1))
then enroll(i)=0;
end;
months=sum(of enroll(*));
if months eq 12 then Met_Criterion=1;
end;
run;

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

chang_y_chung_hotmail_com — Mon, 18 Apr 2011 19:45:34 GMT

Adding a dummy element at both (or either) ends of the array -- as Ksharp does -- is a handy coding technique worth learning. Below finds all the enrollment spans. HTH.

/* test data */

data temp;

  input id dx_month e1-e26;

cards;

101 13  1 1 1  1 1 1  1 1 1  1 1 1  0 0 1  1 1 1  1 1 1  1 1 1  1 1

102 20  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

103 15  0 0 0  0 0 0  0 0 1  1 1 1  1 1 1  1 1 1  1 1 1  1 0 0  0 0

104 14  1 1 1  1 1 1  1 1 1  1 1 1  1 1 1  0 0 0  0 0 0  0 0 0  0 0

105 22  1 1 1  0 0 0  0 0 0  0 0 0  0 0 1  1 1 1  1 1 1  1 1 1  1 0

106 11  0 0 1  1 1 1  1 1 1  1 1 1  1 1 1  1 1 1  1 0 0  0 0 0  0 0

107 20  1 1 1  1 1 1  1 1 1  1 1 1  0 0 1  1 1 1  1 1 1  1 1 1  1 1

108 99  0 0 1  0 1 0  0 0 0  0 0 0  0 0 0  0 0 0  0 0 0  0 0 0  0 1

;

run;

/* find all the countinous enrollment "spans" */

data spans;

  set temp;

  keep id spanId start finish length;

  call missing(sentinel, spanId, start, finish, length);

  array enrolled[1:27] e1-e26 sentinel;

  do m = lbound(enrolled) to hbound(enrolled);

    if start & ^enrolled then do;

      length = m - start;

      finish = m - 1;

      output;

      start = .;

    end; else if ^start & enrolled then do;

      spanId + 1;

      start = m;

    end;

  end;

run;

/* check */

proc print data=spans;

   where length >= 12;

run;

/* on lst

              span

Obs     id     Id     start    finish    length

1     101      1        1       12        12

2     101      2       15       26        12

3     102      1        1       26        26

4     103      1        9       22        14

5     104      1        1       15        15

6     106      1        3       19        17

7     107      1        1       12        12

8     107      2       15       26        12

*/

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

jwsph — Mon, 18 Apr 2011 21:10:56 GMT

Chung - thank you. I saw also that this is picking up the second enrollment spans for the first and last records.

Art297...for the QuestionTwo code, 'months' for ID #107 is 10, but should be 5 months of continuous enrollment (there was a 2-month gap in between). I see why it returned 10 (because of the sum statement). How can I sum starting with e{dx_month-1} back to e{dx_month-12}, but stopping if a '0' is encountered?

Thanks very much to all who have responded...shows the variety of ways to approach it.

Judy

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

art297 — Mon, 18 Apr 2011 21:54:08 GMT

Judy,

Since your specs indicated that you were only interested in finding those records with 12 consecutive months, the only relevant number from the count (for Question 2) was whether it was 12 or not.

Art
----------
> Chung - thank you. I saw also that this is picking
> up the second enrollment spans for the first and last
> records.
>
> Art297...for the QuestionTwo code, 'months' for ID
> #107 is 10, but should be 5 months of continuous
> enrollment (there was a 2-month gap in between). I
> see why it returned 10 (because of the sum
> statement). How can I sum starting with
> e{dx_month-1} back to e{dx_month-12}, but stopping if
> a '0' is encountered?
>
> Thanks very much to all who have responded...shows
> the variety of ways to approach it.
>
> Judy

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

Ksharp — Tue, 19 Apr 2011 01:09:45 GMT

Hi.
Art.T ,that is not to need to add it.Because I have make a variable e_end which is always to equal zero,So the condition in loop will be false finally.
[pre]
do while(j lt dim(e) and e{j}=1);
[/pre]

And For the Second Question,I think it will be easy after yielding my dataset, So you can do it by yourself.

Ksharp Message was edited by: Ksharp

Re: insurance claims: evaluating period of enrollment prior to diagnosis date

jwsph — Tue, 19 Apr 2011 02:32:32 GMT

Art - thanks...I didn't think the calculation through completely before I posted that..I understand what the code is doing now. thank you..

KSharp - thanks for your help also.