topic Re: How to perform Date Look Back on Claims Data in New SAS User

How to perform Date Look Back on Claims Data

Dhuang0925 — Tue, 15 Nov 2022 03:52:33 GMT

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!

Re: How to perform Date Look Back on Claims Data

pink_poodle — Tue, 15 Nov 2022 04:25:46 GMT

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;

Re: How to perform Date Look Back on Claims Data

Dhuang0925 — Tue, 15 Nov 2022 04:48:32 GMT

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?

Re: How to perform Date Look Back on Claims Data

pink_poodle — Wed, 16 Nov 2022 19:39:11 GMT

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.