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
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.
What do you mean by "corresponding to the value in trade"? There are no two values in trade that match.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.