Hi, I am trying to calculate the value of the last measurement taken (according to the date column) divided by the lowest value recorded (according to the measurement column) if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match. The the calculation would be produced in a new column. I am having trouble with this and I would appreciate a solution to this matter.
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
;
I am trying to calculate the value of the last measurement taken (according to the date column)
I don't see this represented in your output table. It seems as if you are using the current measurement (not the last measurement taken) as the nuemrator.
Here is my code:
proc summary data=have nway;
class subject procedure;
var measurement;
output out=stats min=min_measurement;
run;
proc sort data=have;
by subject procedure;
run;
data want;
merge have stats;
by subject procedure;
output = measurement/min_measurement;
run;
I assume you mean
"last measurement taken (according to the date column) for each subject"
"the lowest value recorded (according to the measurement column) for each subject"
Is that correct?
if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match
what happens for rows where there are not two values of SUBJECT that match? such as row 5?
Can you show us the desired output table, please?
Yes that is correct. This is an example of the output.
Subject Type Date Procedure Measurement Output
500 Initial 15 AUG 2017 Invasive 20 20/20
500 Initial 15 AUG 2017 Surface 35 35/35
500 Followup 15 AUG 2018 Invasive 54 54/20
428 Followup 15 AUG 2018 Outer 29 29/10
765 Seventh 3 AUG 2018 Other 13 13/19
500 Followup 3 JUL 2018 surface 98 98/35
428 Initial 3 JUL 2017 Outer 10 10/10
765 Initial 20 JUL 2019 Other 19 19/19
610 Third 20 AUG 2019 Invasive 66 66/17
610 Initial 17 Mar 2018 Invasive 17 17/17
Yes, that is correct. This is an example of the intended output table that I hopes clarifies.
*Intended output table
Subject Type Date Procedure Measurement Output
500 Initial 15 AUG 2017 Invasive 20 20/20
500 Initial 15 AUG 2017 Surface 35 35/35
500 Followup 15 AUG 2018 Invasive 54 54/20
428 Followup 15 AUG 2018 Outer 29 29/10
765 Seventh 3 AUG 2018 Other 13 13/19
500 Followup 3 JUL 2018 surface 98 98/35
428 Initial 3 JUL 2017 Outer 10 10/10
765 Initial 20 JUL 2019 Other 19 19/19
610 Third 20 AUG 2019 Invasive 66 66/17
610 Initial 17 Mar 2018 Invasive 17 17/17 ;
I am trying to calculate the value of the last measurement taken (according to the date column)
I don't see this represented in your output table. It seems as if you are using the current measurement (not the last measurement taken) as the nuemrator.
Here is my code:
proc summary data=have nway;
class subject procedure;
var measurement;
output out=stats min=min_measurement;
run;
proc sort data=have;
by subject procedure;
run;
data want;
merge have stats;
by subject procedure;
output = measurement/min_measurement;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.