lundi 23 mars 2020

Calculating percent change based on columns matching in SAS

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. 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. This is also known as the percent change from nadir. Thank you in advance!

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;

Aucun commentaire:

Enregistrer un commentaire