How to perform Date Look Back on Claims Data

[Dhuang0925]

Hello New SAS User, I am currently working on project using claims information.  Currently I have two tables.

Table 1.

ENROLID INDEX DATE

123              11/02/2018

234              6/05/2017

Table 2. 

ENROLID YEAR ENROL1 ENROL2 ENROL3... ENROL12

123             2017      1             1             0                 1

123             2018      1             1             1                 1

ENROL1 = Janurary

ENROL2 = February etc.. up to ENROL12

I need to know whether ENROLID 123 is continous enrolled 1 year before the index date (11/02/2018).

Thank you inadvance for any help you are able to provide!

[pink_poodle]

Continuous enrollment means enrols for all the months prior to index month are equal 1.
1)
Data tab1;
Set tab1;
Index_month = month(index_date);
Run;

2)
Proc sort data=tab1; by enrolid; run;
Proc sort data=tab2; by enrolid; run;
Data tab2;
Merge tab1 tab2;
By enrolid;
Run;

3)
Data tab3;
Set tab3;
Array e enrol: ;
Months_enrolled = sum(of e[*]);
Run;

Data tab3;
Set tab3;
If months_enrolled < index_month then continuous_enrol = 0;
Else do;
Array e enrol:;
If sum( enrol[1]:enrol[index_month]) < index_month then continuous_enrol = 0;
Else continuous_enrol = 1;
End;
Run;

[Dhuang0925]

Hello Pink_poodle
Thank you so much for replying to my question. I am a very very new SAS user.
I am following your solution up to
"If months_enrolled < index_month then continuous_enrol = 0;
Else do;
Array e enrol:;
If sum( enrol[1]:enrol[index_month]) < index_month then continuous_enrol = 0;
Else continuous_enrol = 1;:"

Do you mind explain a bit further what these steps achieve?

[pink_poodle]

Hi @Dhuang0925,
No problem, I really like this question. Basically, I do not care about the order it which months are. If I sum the “1”s for all months and see that there are fewer of them than my index month from which I look backwards, then I know for sure a person discontinued enrollment. Hence, this statement:
If months_enrolled < index_month then continuous_enrol = 0;
Otherwise, I do not want the sum for all months_enrolled, because there could be some “1”s behind and some ahead of my observation point (index_month), and I would not know.
Instead what I do is sum enrols up until my month
Sum(enrol[1]:enrol[index_month])
Now if the sum is less than my month number, I would know there was a break and continuous_enrol = 0.
Maybe I can just do this last step and not care about total months. Please let me know if you have more questions.
Best wishes.