Dividing within a column based on other columns matching

[AshJuri]

Hi all, I am trying to calculate the current measurement in column “Total" minus the lowest measurement previously recorded in column "Total" where the current measurement in column “Total” corresponding to the value in column "Trade" is less than(<) the minimum measurement in column “Total”  corresponding to the value in column "trade", and if  two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match. To emphasize, the minimum value must be a previously recorded. If the measurement is less than the current measurement but was not recorded previously (according to the “date” column), it does not quality to be subtracted from the current measurement. An example of the output is provided below.

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

Subject Type      Date                Trade                Procedure     Total      
500   Initial    15 AUG 2017            6                   Invasive        20        
500   Initial    15 AUG 2017            9                   Surface         35        
500   Followup   15 AUG 2018            8                   Invasive        54       
428   Followup    15 AUG 2018          56                   Outer           29        
765   Seventh     3 AUG 2018           12                   Other           13     
500   Followup    3 JUL 2018           23                   surface         98    
428   Initial     3 JUL 2017           34                   Outer           10    
765   Initial     20 JUL 2019          4                    Other           19     
610   Third       20 AUG 2019         58                    Invasive        66   
610   Initial     17 Mar 2018         25                    Invasive        17     

*Example of Output;

Subject Type      Date                Trade                Procedure     Total        Output
500   Initial    15 AUG 2017            6                   Invasive        20        20/20
500   Initial    15 AUG 2017            9                   Surface         35        35/35
500   Followup   15 AUG 2018            8                   Invasive        54        54/20
428   Followup    15 AUG 2018          56                   Outer           29       29/10
765   Seventh     3 AUG 2018           12                   Other           13       13/19
500   Followup    3 JUL 2018           23                   surface         98       98/35
428   Initial     3 JUL 2017           34                   Outer           10       10/10
765   Initial     20 JUL 2019           4                   Other           19       19/19
610   Third       20 AUG 2019          58                   Invasive        66       66/17
610   Initial     17 Mar 2018          25                   Invasive        17       17/17

[Kurt_Bremser]

What do you mean by "corresponding to the value in trade"? There are no two values in trade that match.

[Patrick · Accepted solution]

I'm a bit struggling to understand your narrative but below code returns something close to what you show us.

data Have;
	input Subject Type :$12. Date :date9. Trade 
    Procedure :$12. Measurement want $;
	format date date9.;
  procedure=propcase(procedure);
	datalines;
500 Initial 15AUG2017 6 Invasive 20 20/20
500 Initial 15AUG2017 9 Surface 35 35/35
500 Followup 15AUG2018 8 Invasive 54 54/20
428 Followup 15AUG2018 56 Outer 29 29/10
765 Seventh 3AUG2018 12 Other 13 13/19
500 Followup 3JUL2018 23 surface 98 98/35
428 Initial 3JUL2017 34 Outer 10 10/10
765 Initial 20JUL2019 4 Other 19 19/19
610 Third 20AUG2019 58 Invasive 66 66/17
610 Initial 17Mar2018 25 Invasive 17 17/17
;

proc sort data=have;
  by Subject procedure date;
run;

data want(drop=_:);
  set have;
  by Subject procedure date;
  length div $10;
  _lag_Measurement=lag(Measurement);
  if first.procedure then _lag_Measurement=Measurement;
  div=catx('/',Measurement,_lag_Measurement);
run;

proc print;
run;

For the last two rows: Is the difference between your Want and what's in Div caused by incorrect logic or is your sample data incorrect here?

If wrong logic: How would the logic need to be to populate the column as per your sample.

[AshJuri]

Hi, thank you for your feedback. I just wanted to ask if the column "trade" is considered for the reason that the current measurement must have a "trade" value higher than the "trade" value of the minimum measurement if they are to be divided?