Hi @Sv_kini
I have created the below code. Please test it to see whether it corresponds to your need and let me know.
NB: I don't understand why you want to retrieve first and last date for a disease when the "flag" indicating that there were at least 2 outpatient visits >= 7 days apart and each one satisfying the condition is equal to 0.
So the below output doesn't show that.
Best regards,
data raw;
input ID clm_from inpatient o_diab o_angina o_anemia;
informat clm_from ddmmyy10.;
format clm_from ddmmyy10.;
datalines;
1 01/01/2019 0 0 1 1
1 05/01/2019 0 1 1 1
1 10/01/2019 0 0 0 0
1 15/01/2019 0 1 0 0
1 20/01/2019 0 0 1 0
2 05/03/2019 0 1 0 1
2 10/03/2019 0 0 0 1
2 05/04/2019 0 0 0 0
;
run;
/* Retrieve the max number of observation for an ID in macrovariable &count_obs */
proc sql noprint;
select max(count_obs) into: count_obs from (select count(*) as count_obs from raw group by id);
quit;
/* Retrieve the list of diseases = diab, angina, ... in the dataset. raw_col */
proc contents data=raw out=raw_col (keep = name where=(lowcase(name) like 'o_%')) noprint;
run;
proc sort data=raw out=list_id (keep=id) nodupkey;
by ID;
run;
/* Put the list of disease in macrovariable &list_dis */
proc sql noprint;
select dis into:list_dis separated by " " from list_disease;
quit;
/* Macro to create for a specific disease the table containing the flag, the first and the last date */
%macro loop (disease);
/* Identify cases where flag = 1 (<=> >7 days between 2 visits) + the last date */
data dst_&disease._last (keep= id &disease lastdt_o_&disease);
set raw;
by ID;
array _lagID (&count_obs);
array _lago_&disease (&count_obs);
array _lagCLM_&disease. (&count_obs);
%do i=1 %to &count_obs;
_lagID(&i) = lag&i.(ID);
_lago_&disease.(&i) = lag&i.(o_&disease);
_lagCLM_&disease.(&i.) = clm_from - lag&i.(clm_from);
%end;
%do i=1 %to &count_obs;
if _lagID(&i) ne ID or (o_&disease. <1 or _lago_&disease.(&i) <1) then _lagCLM_&disease.(&i) = 0;
%end;
if max(of _lagCLM_&disease.(*)) >=7 then do;
&disease = 1;
output;
end;
rename clm_from = lastdt_o_&disease;
run;
/* Identify the first date */
data dst_&disease._first (keep=id clm_from rename=(clm_from = firstdt_o_&disease));
set raw;
by ID;
where o_&disease = 1;
if first.ID then output;
run;
/* Merge */
proc sql;
create table dataset_&disease as
select a.ID, b.firstdt_o_&disease, a.lastdt_o_&disease, a.&disease
from dst_&disease._last as a left join dst_&disease._first as b
on a.id = b.id;
quit;
%mend;
/* Execute the macro %loop for each disease specified in the raw_col dataset */
data list_disease;
set raw_col;
dis = compress(name,"o_");
rc=dosubl(cats('%loop(',dis,')'));
run;
/* Merge all datasets by ID + put the disease flag to 0 when not equal to 1 */
data want;
merge list_id dataset:;
by ID;
array _dis (*) &list_dis;
do i=1 to dim(_dis);
if _dis(i) = . then _dis(i) = 0;
end;
drop i;
run;
proc print data=want;
id id;
run;
Dataset WANT: output
... View more