07-25-2013 09:27 AM
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:
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|
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.
07-25-2013 10:25 AM
This operation is straightforward with SQL
create table want as
b.date as previousDate,
b.db as previousDb
have as a inner join
have as b
on a.patient=b.patient and
a.db <= b.db-10;
select * from want;