Hi hope this helps,
data DB;
input ID :$20. Admission :date09. Discharge :date09. Variable1 Variable2;
cards;
166 16FEB2019 26FEB2019 1 0
170 22FEB2017 07MAR2017 1 0
170 22FEB2017 07MAR2017 0 1
170 30JAN2019 04MAR2019 0 0
313 03MAR2016 10MAR2016 1 0
313 03MAR2016 10MAR2016 0 1
313 12DEC2019 15DEC2019 1 0
215 22DEC2014 25DEC2014 1 1
;
run;
proc sort;
by ID Admission Discharge;
run;
/* Step 1: Create a temporary dataset with the maximum value of Variable2 for each combination of ID, Admission, and Discharge */
proc sql;
create table Temp as
select ID, Admission, Discharge, max(Variable2) as MaxVar2
from DB
group by ID, Admission, Discharge;
quit;
/* Step 2: Merge the temporary dataset back with the original dataset to update Variable2 */
data DB1;
merge DB(in=a drop=variable2) Temp(in=b);
by ID Admission Discharge;
if a;
if Variable1 = 1 then Variable2 = MaxVar2;
if cmiss(variable2)=1 then variable2=0;
drop MaxVar2;
run;
... View more
