topic Re: Calculation of values that rely on a date variable in SAS Data Management

Calculation of values that rely on a date variable

AshJuri — Fri, 03 Apr 2020 17:12:42 GMT

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 
;

Re: Calculation of values that rely on a date variable

PaigeMiller — Fri, 03 Apr 2020 17:30:34 GMT

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?

Re: Calculation of values that rely on a date variable

AshJuri — Fri, 03 Apr 2020 17:42:23 GMT

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

Re: Calculation of values that rely on a date variable

AshJuri — Fri, 03 Apr 2020 17:54:56 GMT

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 ;

Re: Calculation of values that rely on a date variable

PaigeMiller — Fri, 03 Apr 2020 17:58:47 GMT

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;