Retain flag for new calculation if present in one observation

[serena13lee]

Hi, I am calculating BMI from a patient's Height and Weight per patient per date. For each BMI calculation I would like to keep Patient, VisitDate, Visit, and bflag information. There is a typo for patient B on Jan12019 where weight is not marked with the same Visit and bflag info that Height is. I would like for the BMI to have that info. Is there a way I can keep the visit and bflag? 

data in;
input Patient $     VisitDate $   Value     Unit  $   Type $  Visit $ bflag $;
cards;
A Jan12019 1 m Height baseline Y
A Jan12019 50 kg Weight baseline Y 
A Jan52019 2 m Height visit1 .
A Jan52019 55 kg Weight visit1 .
B Jan12019 1 m Height baseline Y
B Jan12019 50 kg Weight . .
B Jan52019 55 kg Weight baseline Y
;

proc sort data=in;
by patient VisitDate;
run;

data have;
set in;
by patient VisitDate;
retain height_;
if first.VisitDate then height_=.;
if type='Height' then height_=Value;
if last.VisitDate then bmi=value/height_**2;
output;
if bmi ne . then do;
type='BMI';
value=bmi;
unit='kg/m2';
output;
end;
drop height_ bmi;
run;

data want;
input Patient $     VisitDate $   Value     Unit  $   Type $  Visit $ bflag $;
cards;
A Jan12019 1 m Height baseline Y	
A Jan12019 50 kg Weight baseline Y	
A Jan12019 50 kg/m2 BMI baseline Y	
A Jan52019 2 m Height visit1 .	
A Jan52019 55 kg Weight visit1 .	
A Jan52019 13.75 kg/m2 BMI visit1 .		
B Jan12019 1 m Height baseline Y	
B Jan12019 50 kg Weight . .		
B Jan12019 50 kg/m2 BMI baseline Y		
B Jan52019 55 kg Weight baseline Y

; 

[Reeza]

Is this a one off typo or a systemic that you need to correct for other records as well? You handle the two types of cases differently.

[serena13lee]

Hi Reeza, I believe this is a systemic issue since I have seen other patients with this issue. Please let me know if you have any further questions. Thanks!

[Reeza]

If it's a systemic issue, it would be best if you posted a few examples that illustrate the range you're trying to deal with, ie one that has height missing instead of weight or multiple missing. You should also fix those dates but that's something we can help you with once you post your new data.

[serena13lee]

Hi Reeza, thanks for mentioning that. So I took a look and it appears that all of the range of issues are the exact same as the situation with B. I.e. there are only situations where by patient, by visitDate that Height has visit and blflag and Weight does not. Thanks! As for date, is there a preferred format?

[heffo]

Not exactly what you want, but close maybe a start? 

data in;
	input Patient $     VisitDate $   Value     Unit  $   Type $  Visit $ bflag $;
	cards;
A Jan12019 1 m Height baseline Y
A Jan12019 50 kg Weight baseline Y 
A Jan52019 2 m Height visit1 .
A Jan52019 55 kg Weight visit1 .
B Jan12019 1 m Height baseline Y
B Jan12019 50 kg Weight . .
B Jan52019 55 kg Weight baseline Y
;

proc sort data=in;
	by patient descending  VisitDate;
run;

data want;
	set in;
	by patient descending VisitDate;
	retain height_ height_;

	if first.VisitDate then do;
		height_=.;
		weight_=.;
	end;
	if type='Height' then
		height_=Value;
	else if type='Weight' then
		weight_=Value;

	if last.VisitDate then do;
		if weight_ = . then weight_ = lag(value);
		if Visit = . then Visit = lag(Visit);
		if bflag = . then bflag = lag(bflag);
		bmi=weight_/height_**2;
	end;
	output;

	if bmi ne . then
		do;
			type='BMI';
			value=bmi;
			unit='kg/m2';
			output;
		end;

	drop height_ weight_ bmi;
run;
proc sort data=want;
	by patient VisitDate;
run;

[serena13lee]

Thanks for your solution! It was indeed very close and lead to mine 🙂

[serena13lee · Accepted solution]

data in;
	input Patient $     adtm $   Value     Unit  $   Type $  avisit $ bflag $;
	cards;
A Jan12019 1 m Height baseline Y
A Jan12019 50 kg Weight baseline Y 
A Jan52019 2 m Height visit1 .
A Jan52019 55 kg Weight visit1 .
B Jan12019 1 m Height baseline Y
B Jan12019 50 kg Weight . .
B Jan52019 55 kg Weight baseline Y
;

proc sort data=in;
	by patient adtm descending Type;
run;

data want;
	set in;
	by patient adtm descending Type;
	retain weight_ height_;
	if first.adtm then do;
		weight_=.;
		height_=.;
	end;
	if type='Weight' then
		weight_=Value;
	else if type='Height' then
		height_=Value;

	if last.adtm then do;
		bmi=weight_/height_**2;
	end;
	output;
	if bmi ne . then
		do;
			type='BMI';
			value=bmi;
			unit='kg/m2';
			output;
		end;
	drop height_ weight_ bmi;
run;