topic Move values of a variable depending on two conditions in New SAS User

Move values of a variable depending on two conditions

NewUsrStat — Fri, 17 Jan 2025 08:53:23 GMT

Hi guys,

suppose to have the following very sparse matrix I received and I have to manage.

data DB;
  input ID :$20. Class1 Class2 Class3 Class4 Index AgeStart AgeEnd Adm :date09. Disch :date09. ; 
  format Adm date9. Disch date9.;
cards;
0001  3.4      .        .       .        1       65        69    29SEP2014  09OCT2014
0001   .       .        .       .        .       65        69    04MAY2015  12MAY2015
0001   .       .        .       .        .       65        69    31MAY2016  08JUN2016
0001   .      4.9       .       .        1       70        74    12DEC2016  16DEC2016
0001   .       .        .       .        .       70        74    19JUN2017  27JUN2017
0001   .       .        .       .        .       70        74    25JAN2018  01FEB2018
0001   .       .        .       .        .       70        74    26NOV2018  05DEC2018
0001   .       .        .       .        .       70        74    31MAY2019  07JUN2019
0001  3.4     4.9      0.5      .        .       70        74    27MAY2020  03JUN2020
0002   .       .       3.7      .        1       75        79    01OCT2016  05OCT2016
0002   .       .       3.7     0.1       .       75        79    03JUN2017  12JUN2017
; 
run;

Is there a way to get the following?


data DB1;
  input ID :$20. Class1 Class2 Class3 Class4 Index AgeStart AgeEnd Adm :date09. Disch :date09. ; 
  format Adm date9. Disch date9.;
cards;
0001  3.4      .        .       .        1       65        69    29SEP2014  09OCT2014
0001   .       .        .       .        .       65        69    04MAY2015  12MAY2015
0001   .       .        .       .        .       65        69    31MAY2016  08JUN2016
0001   .      4.9      0.5      .        1       70        74    12DEC2016  16DEC2016
0001   .       .        .       .        .       70        74    19JUN2017  27JUN2017
0001   .       .        .       .        .       70        74    25JAN2018  01FEB2018
0001   .       .        .       .        .       70        74    26NOV2018  05DEC2018
0001   .       .        .       .        .       70        74    31MAY2019  07JUN2019
0001  3.4     4.9       .       .        .       70        74    27MAY2020  03JUN2020
0002   .       .       3.7     0.1       1       75        79    01OCT2016  05OCT2016
0002   .       .       3.7      .        .       75        79    03JUN2017  12JUN2017
; 
run;

The idea is the following:

look for example at ID 0001. Values 3.4 and 4.9 appear where Index = 1. Value 0.5 at Class3 not. Is there a way to move 0.5 at line 4 where Index = 1? Why not at line 1? Because "AgeStart" (and "AgeEnd") is different. It should be moved where Index = 1 of the same AgeStart (and "AgeEnd") group.

The same principle for ID 0002.

Thank you in advance

Re: Move values of a variable depending on two conditions

quickbluefish — Fri, 17 Jan 2025 16:06:19 GMT

Why are you moving Class3 but not Class1 in the example above (i.e., the 3.4)?

Re: Move values of a variable depending on two conditions

NewUsrStat — Fri, 17 Jan 2025 17:17:31 GMT

Because 3.4 is a duplicated value.

Re: Move values of a variable depending on two conditions

Ksharp — Sat, 18 Jan 2025 08:16:48 GMT

data DB;
  input ID :$20. Class1 Class2 Class3 Class4 Index AgeStart AgeEnd Adm :date09. Disch :date09. ; 
  format Adm date9. Disch date9.;
cards;
0001  3.4      .        .       .        1       65        69    29SEP2014  09OCT2014
0001   .       .        .       .        .       65        69    04MAY2015  12MAY2015
0001   .       .        .       .        .       65        69    31MAY2016  08JUN2016
0001   .      4.9       .       .        1       70        74    12DEC2016  16DEC2016
0001   .       .        .       .        .       70        74    19JUN2017  27JUN2017
0001   .       .        .       .        .       70        74    25JAN2018  01FEB2018
0001   .       .        .       .        .       70        74    26NOV2018  05DEC2018
0001   .       .        .       .        .       70        74    31MAY2019  07JUN2019
0001  3.4     4.9      0.5      .        .       70        74    27MAY2020  03JUN2020
0002   .       .       3.7      .        1       75        79    01OCT2016  05OCT2016
0002   .       .       3.7     0.1       .       75        79    03JUN2017  12JUN2017
; 
run;
data 
class1(keep=id class1 AgeStart AgeEnd rename=(class1=_class1) )
class2(keep=id class2 AgeStart AgeEnd rename=(class2=_class2) )
class3(keep=id class3 AgeStart AgeEnd rename=(class3=_class3) ) 
class4(keep=id class4 AgeStart AgeEnd rename=(class4=_class4) )
;
 set DB;
 if not missing(Class1) then output class1;
 if not missing(Class2) then output class2;
 if not missing(Class3) then output class3;
 if not missing(Class4) then output class4;
run;
proc sort data=class1 nouniquekey uniqueout=_class1;
 by id _class1;
run;
proc sort data=class2 nouniquekey uniqueout=_class2;
 by id _class2;
run;
proc sort data=class3 nouniquekey uniqueout=_class3;
 by id _class3;
run;
proc sort data=class4 nouniquekey uniqueout=_class4;
 by id _class4;
run;


data want;
 if _n_=1 then do;
  if 0 then set DB _class:;
  declare hash h1(dataset:'_class1');
  h1.definekey('id','AgeStart','AgeEnd');
  h1.definedata('_class1');
  h1.definedone();
  declare hash h2(dataset:'_class2');
  h2.definekey('id','AgeStart','AgeEnd');
  h2.definedata('_class2');
  h2.definedone();
  declare hash h3(dataset:'_class3');
  h3.definekey('id','AgeStart','AgeEnd');
  h3.definedata('_class3');
  h3.definedone();
  declare hash h4(dataset:'_class4');
  h4.definekey('id','AgeStart','AgeEnd');
  h4.definedata('_class4');
  h4.definedone();
end;
set DB;
if Index=1 and h1.find()=0  then class1=_class1;
if Index=1 and h2.find()=0  then class2=_class2;
if Index=1 and h3.find()=0  then class3=_class3;
if Index=1 and h4.find()=0  then class4=_class4;
if Index=. and h1.check()=0 then class1=.;
if Index=. and h2.check()=0 then class2=.;
if Index=. and h3.check()=0 then class3=.;
if Index=. and h4.check()=0 then class4=.;
drop _class:;
run;