Move values of a variable when missing values of another variable(s)

NewUsrStat — Fri, 29 Nov 2024 12:52:43 GMT

Hi guys,

suppose to have the following:

data have;
  input ID :$20. Admission :date09. Discharge :date09. Variable1 Variable2 Index; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2015 20JAN2015 1  0  0
0001 21FEB2015 31DEC2015 0  1  1
0001 21FEB2015 31DEC2015 .  .  1
0001 01JAN2019 31DEC2019 0  1  0
0002 01JAN2015 31DEC2015 1  0  0
0002 01JAN2019 31OCT2019 1  0  0
0002 01JAN2019 31OCT2019 .  .  1
;

Is there a way to get the following for repeated (only!) dates?


data have1;
  input ID :$20. Admission :date09. Discharge :date09. Variable1 Variable2 Index; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2015 20JAN2015 1  0  0
0001 21FEB2015 31DEC2015 0  1  1
0001 21FEB2015 31DEC2015 .  .  .
0001 01JAN2019 31DEC2019 0  1  0
0002 01JAN2015 31DEC2015 1  0  0
0002 01JAN2019 31OCT2019 1  0  1
0002 01JAN2019 31OCT2019 .  .  .
;

In other words if Index = 1 where Variable1 and Variable2 = . then the value of Index should be moved where not missing(Variable1, Variable2). If already there (e.g., ID = 0001 at 21FEB2015) then it should be set to missing when Variable1 and Variable2 = .

Thank you in advance

Re: Move values of a variable when missing values of another variable(s)

antonbcristina — Fri, 29 Nov 2024 16:43:57 GMT

Hi @NewUsrStat, here's a quick solution:

1. Create flags which capture the index for the duplicate dates which have Variable1=. and Variable2 =. and Index = 1

data have_flags;
	set have;
	by id admission discharge;
	if variable1 =. and variable2 =. and index^=. then flag=index;
	if flag=1;
	keep id admission discharge flag;
run;

2. Merge have_flags back in with the original dataset by id, admission and discharge dates. Using the logic you described, assign index=. or index=flag:

data want;
	merge have have_flags;
	by id admission discharge;
	if variable1=. and variable2=. and flag=1 then index=.;
	else if variable1^=. and variable2^=. and index=0 and flag^=. then index=flag;
	drop flag;
run;

Hope this helps!

topic Move values of a variable when missing values of another variable(s) in New SAS User

Move values of a variable when missing values of another variable(s)

Re: Move values of a variable when missing values of another variable(s)