Calculating percent change based on matching columns

[AshJuri]

Hi everyone,

data have;
input Subject Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
format date yymmdd10.;
datalines;

500   Initial    15 AUG 2017      Invasive    20 
500   Initial    15 AUG 2017     Surface      35 
500   Followup   15 AUG 2018     Invasive     54 
428   Followup    15 AUG 2018      Outer      29 
765   Seventh     3 AUG 2018      Other       13 
500   Followup    3 JUL 2018      Surface     98 
428   Initial     3 JUL 2017     Outer        10 
765   Initial     20 JUL 2019     Other       19 
610   Third       20 AUG 2019     Invasive    66 
610   Initial     17 Mar 2018     Invasive    17 
;

data want (drop=rc _Measurement);
   if _N_ = 1 then do;
      declare hash h (dataset : "have (rename=(Measurement=_Measurement) where=(Type='Initial'))");
      h.definekey ('Subject');
      h.definedata ('_Measurement');
      h.definedone();
   end;

   set have;
   _Measurement=.;

   if Type ne 'Initial' then rc = h.find();
   NewMeasurement = ifn(Measurement=., ., sum (Measurement, -_Measurement));
run;

This code currently subtracts the initial (Type column) measurement from the other measurements if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match. It produces a new column with the calculations. This is the absolute change in measurements.

I want to add to this code so that the value of the latest (date column) measurement taken is divided by the lowest measurement recorded if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match and the calculation is produced in a new column. This is also known as the percent change from nadir. Thank you in advance!

[TomKari]

Why does record _n_=6 (Subject 500, Procedure Surface) have NewMeasurement = 78?
The initial "Surface" record has Measurement = 35, and _n_ = 6 has Measurement = 98.
Shouldn't NewMeasurement be 63?

Tom

[AshJuri]

Hi, thank you for your reply. Would you have a suggestion for how to fix this error as well as for the additional program?

[TomKari]

Please see if this program gives you the results you're expecting for your existing program. If it's okay, I'll try to add the extension. I'm more comfortable using "first." and "last." logic, so they tend to be my go-tos. You wouldn't want to see the results if I tried to do it with a hash! I believe it will fail if there is more than one "Initial".

Tom

data Have;
	set Have;

	if Type = "Initial" then
		_SortFlag = 0;
	else _SortFlag = 1;
run;

proc sort;
	by Subject Procedure _SortFlag Date;
run;

data Want;
	retain _InitialMeasurement;
	set Have;
	by Subject Procedure;

	if first.Procedure then
		do;
			if _SortFlag = 0 then
				do;
					NewMeasurement = Measurement;
					_InitialMeasurement = Measurement;
				end;
			else _InitialMeasurement = 0;
		end;
	else NewMeasurement = Measurement - _InitialMeasurement;
run;

[AshJuri]

Hi, thank you for your reply. It works! I was also trying to add the calculation
"NewMeasurementPercent= (SUMLDIAM/_InitialMeasurement)*100" but the statement "else _InitialMeasurement = 0;" causes a division by zero error. Do you know how I can fix that?

[AshJuri]

Would you be able to provide the extension?

[TomKari · Accepted solution]

Give this a try. Test carefully! The limited number of cases that you provided in your post make it difficult to be sure that this is meeting your needs.

Tom

data Have;
	input Subject Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
	format date yymmdd10.;
	datalines;

500   Initial    15 AUG 2017      Invasive    20 
500   Initial    15 AUG 2017     Surface      35 
500   Followup   15 AUG 2018     Invasive     54 
428   Followup    15 AUG 2018      Outer      29 
765   Seventh     3 AUG 2018      Other       13 
500   Followup    3 JUL 2018      Surface     98 
428   Initial     3 JUL 2017     Outer        10 
765   Initial     20 JUL 2019     Other       19 
610   Third       20 AUG 2019     Invasive    66 
610   Initial     17 Mar 2018     Invasive    17 
;

data Have;
	set Have;

	if Type = "Initial" then
		_SortFlag = 0;
	else _SortFlag = 1;
run;

proc sort;
	by Subject Procedure _SortFlag Date;
run;

data Want;
	retain _InitialMeasurement _LowMeasurement;
	set Have;
	by Subject Procedure;

	if first.Procedure then
		do;
			_LowMeasurement = 10**15;

			if _SortFlag = 0 then
				do;
					NewMeasurement = Measurement;
					_InitialMeasurement = Measurement;
				end;
			else _InitialMeasurement = 0;
		end;
	else NewMeasurement = Measurement - _InitialMeasurement;
	_LowMeasurement = min(Measurement, _LowMeasurement);

	if last.Procedure then
		LowMeasurement = Measurement / _LowMeasurement;
run;