Update values of a variable based on another variable

[NewUsrStat]

Hi guys, 

suppose to have the following: 

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Index1 Index2;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017  1  1
0001  22FEB2018 03MAR2018  0  1 
0001  30JAN2019 04MAR2019  0  1
0002  01DEC2018 14DEC2018  1  1 
0002  25DEC2018 02JAN2019  0  1
0003  09JAN2016 25JAN2016  1  1
0003  29JAN2018 12FEB2018  0  1
0004  02FEB2014 12MAR2014  1  0
0004  02DEC2018 11SEP2019  0  1
;run;

Is there a way to get the following? 

data DB1;
  input ID :$20. Admission :date09. Discharge :date09. Index1 Index2;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017  1  1
0001  22FEB2018 03MAR2018  0  0 
0001  30JAN2019 04MAR2019  0  0
0002  01DEC2018 14DEC2018  1  1 
0002  25DEC2018 02JAN2019  0  0
0003  09JAN2016 25JAN2016  1  1
0003  29JAN2018 12FEB2018  0  0
0004  02FEB2014 12MAR2014  1  0
0004  02DEC2018 11SEP2019  0  1
;run;

In other words the column Index2 should be updated to have "1" only if "1" is present in column Index1 at the same row otherwise 0. This only if for each ID there is more than "1" in variable Index2 (i.e., "1" is repeated). For example, nothing should happen for ID 0004. 

Thank you in advance

[Tom]

The normal way to change a variable's value is an assignment statement.

Sounds like you want to set INDEX2 to be TRUE when INDEX1 and INDEX2 are both TRUE and set it FALSE otherwise.

data db1;
  set db;
  index2= (index1 and index2);
run;

If you had coded INDEX1 and INDEX2 in some other way you might have to resort to using IF/THEN logic to set the values.  Say if you had made character variables with values of Y or N then code might look like this:

data db1;
  set db;
  if index1='N' then index2='N';
run;

[NewUsrStat]

Hi Tom, yes I know but the "problem" regards ID 0004. I cannot state: if index1='N' then index2='N';

[Tom]

---

@NewUsrStat wrote:
Hi Tom, yes I know but the "problem" regards ID 0004. I cannot state: if index1='N' then index2='N';

---

If the condition is something else then change it.

You need to explain the logic better but it sounds like you first need to calculate some ID level new variable to help with the condition.  For example you might want the MAX() of INDEX1 (or was it INDEX2?) which is essentially if ANY of the values are yes.  Or perhaps the SUM() (which is essentially a COUNT of the number of yes values).

PROC SQL will make that easy.

proc sql;
create table step1 as 
  select *
          , max(index1) as any_index1
          , sum(index1) as count_index1
          , max(index2) as any_index2
          , sum(index2) as count_index2
  from db
  group by ID
;
quit;

Now can you write your IF statement using some combination of the two original variables and the four new calculated variables?

[PaigeMiller · Accepted solution]

---

@NewUsrStat wrote:

In other words the column Index2 should be updated to have "1" only if "1" is present in column Index1 at the same row otherwise 0. This only if for each ID there is more than "1" in variable Index2 (i.e., "1" is repeated). For example, nothing should happen for ID 0004. 

---

Try this. I have left a part of the IF statement blank because I think you should be able to fill in the necessary code. Also in your description above you aren't looking for index2 to have the value "1", you are looking for index2 to have the value 1 because index2 is numeric.

proc summary data=db;
    class id;
    var index2;
    output out=_sums_ sum=sum_index2;
run;
data db2;
    merge db1 _sums_;
    by id;
    if sum_index2>1 and index1=1 then ___; 
    else if sum_index2>1 and index1=0 then ____;
run;