Hi @sri21592
Here is an attempt to compute the various flags.
According to you data, it is not logical to set the Enroll_Continous_Months to 1 and No.of_times_discontinued to 2 for patient 103.
In addition, for patient 102, Total_Elig_enrolled_months should be 6 as well as Total_missing_months.
data have;
infile datalines dlm="09"x;
input Person_id region_code Enroll_month YYMMn6.;
format Enroll_month YYMMn6.;
datalines;
101 10001 201712
101 10001 201801
101 10001 201802
101 10001 201803
101 10001 201804
101 10001 201812
102 10003 201712
102 10003 201805
102 10003 201806
102 10003 201807
102 10003 201808
102 10003 201809
102 10003 201810
103 10009 201812
;
run;
/* Preparation */
%let start = 201712;
/* List all participants: dataset "list" */
proc sql;
create table list as
select distinct Person_id, region_code
from have;
quit;
/* Retrieve all months and flag observations accordingly: dataset "have_all"*/
/* flag_Enroll_month = set the month of enrollment to 1 */
/* flag_Enroll = set months of participation to 1 */
data list_month;
set list;
format Enroll_month YYMMn6.;
do i=1 to 13;
Enroll_month = intnx('month',input("&start",YYMMn6.),i-1,"b");
output;
end;
drop i;
run;
data have_flag;
set have;
by Person_id region_code Enroll_month;
if first.region_code then flag_Enroll_month = 1;
else flag_Enroll_month = .;
run;
data enroll;
set have_flag;
where flag_Enroll_month=1;
drop flag_Enroll_month;
rename Enroll_month = initial_month;
run;
data have_pre_all;
merge list_month (in=x) have_flag (in=y);
by Person_id region_code Enroll_month;
if y=1 then flag_enroll = 1;
else flag_enroll = 0;
format Enroll_month YYMMn6.;
run;
data have_all;
merge have_pre_all enroll;
by Person_id region_code;
run;
/* Flag New_enrollment */
proc sql;
create table want1 as
select Person_id, region_code,
case when put(Enroll_month,YYMMn6.) = "&start" then 'No'
else 'Yes'
end as New_enrollment
from have_all
where flag_Enroll_month = 1;
run;
/* Flag Total_Elig_Enrolled_Months
Enrolled_all_12_months
Total_missing_months */
proc sql;
create table want2 as
select Person_id, region_code,
sum(flag_enroll) as Total_Elig_Enrolled_Months,
12 - sum(flag_enroll) as Total_missing_months,
case when sum(flag_enroll) < 12 then 'No'
when sum(flag_enroll) = 12 then 'Yes'
end as Enrolled_all_12_months
from have_all
where put(Enroll_month,YYMMn6.) ne "&start"
group by Person_id, region_code;
quit;
/* Flag Flag No.of_times_discontinued */
data have_disc;
set have_all;
by Person_id region_code Enroll_month;
_lag = lag(flag_enroll);
if flag_enroll=0 and _lag=1 then output;
run;
proc sql;
create table want3 as
select Person_id, region_code, count(*) as No_of_times_discontinued
from have_disc
group by Person_id, region_code;
quit;
/* Enroll_Continous_months */
data have_continous;
set have_all (where=(Enroll_month>initial_month));
by Person_id region_code flag_enroll notsorted;
if first.flag_enroll then count = 0;
count + 1;
run;
proc sql;
create table want4 as
select Person_id, region_code, max(count) as Enroll_Continous_months
from have_continous
where flag_enroll=1
group by Person_id, region_code;
quit;
/* Final dataset */
data want;
merge want1 want2 want3 want4;
run;
... View more
