Working with a date column corresponding to values in another

[AshJuri]

Hi, I would like to add on to this code so that the value of the last measurement according to the "date" column in the "measurement" column is divided by the lowest value recorded in the measurement column and the result forms a new column. The current working code adds a column that subtracts the initial measurements from the other measurements in the "measurement" column if the "subject", "type" and "procedure" columns match. 

data have;
input Subject Type Date $ 5-12 Procedure $ 15-22 Measurement;
datalines;
500   Initial    15 AUG 2017      Invasive     20 
500   Initial    18 SEPT 2018     Surface      35 
500   Followup   12 SEPT 2018   Invasive   54 
428   Followup    2 JUL 2019      Outer        29 
765   Seventh     3 JUL 2018      Other        13 
500   Followup    6 NOV 2018     Surface     98 
428   Initial     23 FEB 2018        Outer        10 
765   Initial     20 AUG 2019        Other        19 
610   Third       21 AUG 2018      Invasive    66 
610 Initial       27 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;

[PGStats · Accepted solution]

Your example data seems inadequate as every subject/date/procedure combination is unique. Here is a SQL solution with extra data added:

data have;
input Subject Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
format date yymmdd10.;
datalines;
500   Initial    15 AUG 2017      Invasive     20 
500   Initial    18 SEPT 2018     Surface      35 
500   Followup   12 SEPT 2018   Invasive   54 
428   Followup    2 JUL 2019      Outer        29 
765   Seventh     3 JUL 2018      Other        13 
500   Followup    6 NOV 2018     Surface     98 
428   Initial     23 FEB 2018        Outer        10 
765   Initial     20 AUG 2019        Other        19 
610   Third       21 AUG 2018      Invasive    66 
610   Initial     27 Mar 2018       Invasive     17 
999   Dummy       17 mar 2020     Some   1
999   Dummy       18 mar 2020     Some   2
999   Dummy       19 mar 2020     Some   3
;

proc sql;
create table want as
select *,
	(select max(measurement) 
	 from have 
	 where subject=a.subject and type=a.type and procedure=a.procedure 
	 having date = max(date)) / min(measurement) as ratio
from have as a
group by subject, type, procedure
order by subject, date;
quit;

[AshJuri]

Hi, I am getting the message "ERROR: Subquery evaluated to more than one row." I am not sure what line is causing this.