How to see if subsequent test scores improved over the max of any previous scores

[Jess_moore27]

trarrWe're using SAS 9.3

We have a dataset with a series of hearing test scores, sorted by patient ID, ear (left or right) and test date. For each patient, we need to find exams in which the test result, measured in decibels, was better (i.e. lower) than any prior exam by 10dB or more per patient, per ear.

For example, Patient X has had 7 exams in his left ear in and the original data record would look like this:

Patient	Ear	Date	Measurement (dB)
X	Left	Date1	10
X	Left	Date2	15
X	Left	Date3	26
X	Left	Date4	10
X	Left	Date5	20
X	Left	Date6	35
X	Left	Date7	10

I want to know, for each exam, if any of the prior exams were worse by at least 10dB. To do this, I will probably want to figure out what the max of the prior exams was and the difference between the recorded result and that maximum for each exam.

Patient	Ear	Date	Meaurement (dB)	Max of prior exams	Difference	Improvement
X	Left	Date1	10	--	--	--
X	Left	Date2	15	10	5	No
X	Left	Date3	26	15	11	No
X	Left	Date4	10	26	-16	Yes
X	Left	Date5	20	26	-6	No
X	Left	Date6	35	26	9	No
X	Left	Date7	10	35	-25	Yes

I tried doing this using Proc Transpose and the Max command in subsequent data step but each patient can have up to 22 tests, with some having just a few tests and some having as many as 22 tests performed, so this method seems cumbersome. It seems I should be able to do this with an array but I can't figure out all the steps. I can't figure out how to get the max of previous tests for each additional test performed rather than just getting the max of all the tests performed. Any help with this would be greatly appreciated, even if just to get in the right direction.

Cheers,

Jessica

[PGStats]

This operation is straightforward with SQL

proc sql;

create table want as

select

     a.patient,

     a.ear,

     a.date,

     b.date as previousDate,

     a.db,

     b.db as previousDb

from

     have as a inner join

     have as b

          on a.patient=b.patient and

               a.ear=b.ear and

               a.date>b.date and

               a.db <= b.db-10;

select * from want;

quit;

PG