Retaining max values over multiple columns

user_16367802 0
Viewed 23

I have a dataset like below, and want to collapse a subject so that I can see if they were diagnosed with a disease at all within the past 3 years using SAS. Disease1-3 are binary yes/no flags.

For example - for subject a in 2021, since they had all 3 diseases in the prior year of 2020, they should also have flags for all those diseases in 2021 and 2022.

subject year disease1 disease2 disease 3
a 2020 1 1 1
a 2021 0 0 0
a 2022 0 0 0
b 2020 0 1 0
b 2021 1 0 0
b 2022 0 0 1

I'm hoping it would look something like this.

subject year disease1 disease2 disease 3
a 2020 1 1 1
a 2021 1 1 1
a 2022 1 1 1
b 2020 0 1 0
b 2021 1 1 0
b 2022 1 1 1

What would be the best way about going to do this? I've tried using a do loop and the retain statement, but get stuck due to the fact that there are multiple columns to consider (disease1-disease3).

sas
2 Answers

Store the max value of disease into a temporary variable. Retain this for each group. If the stored max value is ever 1, set all subsequent values to be 1 for each disease.

data want;
    set have;
    by subject year;

    array disease[*] disease1-disease3;
    array disease_max[3] _temporary_;
    retain disease_max;

    do i = 1 to dim(disease);
        if(first.subject) then disease_max[i] = 0;  /* Reset disease max counter for each subject */
        if(disease[i] = 1) then disease_max[i] = 1; /* Store max disease value */
        if(disease_max[i] = 1) then disease[i] = 1; /* Set disease to 1 if disease_max is 1 */
    end;

    drop i;
run;
user_53427000 
data have;
input subject $ year disease1 disease2 disease3;
datalines;
a 2020 1 1 1
a 2021 0 0 0
a 2022 0 0 0
b 2020 0 1 0
b 2021 1 0 0
b 2022 0 0 1
;

data temp;
   set have;
   array d disease:;
   do over d; 
      if d = 0 then d = .;
   end;
run;

data want;
   update temp(obs=0) temp;
   by subject;
   array d disease:;
   do over d; 
      if d = . then d = 0;
   end;
   output;
run;
user_40449360
Related