sas

Move values of a variable where not missing the value of another one

suppose to have the following:

data have;
  input ID :$20. Admission :date09. Discharge :date09. All  Variable1 Variable2; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2015 20JAN2015 2.45  3.5    .
0001 21FEB2015 31DEC2015   .    .    2.3
0002 01JAN2015 31DEC2015 2.00  7.5    .
0002 14FEB2020 21FEB2020   .    .    1.0
;

Is there a way to get the following?

data want;
  input ID :$20. Admission :date09. Discharge :date09. All  Variable1 Variable2; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2015 20JAN2015 2.45  3.5   2.3
0001 21FEB2015 31DEC2015   .    .     .
0002 01JAN2015 31DEC2015 2.00  7.5   1.0
0002 14FEB2020 21FEB2020   .    .     .
;

In other words if and where it happens I would like to move the value of Variable2 where not missing(All).

Thank you in advance

Solution

  • You can do a self-update statement by ID and then set the Variable1-Variable2 to missing when all is missing.

    data want;
    update have
           have
    ;
    by id;

    if(all = .) then call missing(Variable1, Variable2);
run;

    ID      Admission   Discharge   All    Variable1   Variable2
0001    21FEB2015   31DEC2015   2.45   3.5         2.3
0001    21FEB2015   31DEC2015   .      .           .  
0002    14FEB2020   21FEB2020   2      7.5         1
0002    14FEB2020   21FEB2020   .      .           .

    The update statement is similar to a merge but it handles missing values differently. To learn more about what the Update statement does, see SAS documentation on the UPDATE statement.