@NewUsrStat wrote: So: "Variable1 =1 always at the first..." is a statement. Replicated rows are identical: for ID, Admission and Discharge.
Still do not know what that means, but since your example is not changing the value of VARIABLE1 perhaps we can just ignore it?
If we assume that the source data has a maximum of one observation per group that has VARIABLE1 set to 1. And if we assume that your goal is to modify the values of VARIABLE2 only in the groups that ever have VARIABLE2 set to 1 then perhaps this SQL query is what you want?
proc sql;
create table db1 as
select id ,admission ,discharge ,variable1
, case when (max(variable2)) then variable1
else variable2
end as variable2
from db
group by id, admission, discharge
order by id, admission, discharge, variable1 desc
;
quit;
The SELECT statement lists the variables to create in the NEW dataset. We copy most of the existing variables as they are. Only VARIABLE2 needs some change. So when vairable2 is ever true for the group then use the values of variable1 (so that it has 1 on the first and zero everywhere else) otherwise leave variable2 as it was.
Make sure to order the repeating observations by descending value of VARIABLE1.
Results:
... View more
