Help using Base SAS procedures

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

Reply
N/A
Posts: 1

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

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:

PatientEarDateMeasurement (dB)
XLeftDate110
XLeftDate215
XLeftDate326
XLeftDate410
XLeftDate520
XLeftDate635
XLeftDate710

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.

PatientEarDateMeaurement (dB)Max of prior examsDifferenceImprovement
XLeftDate110------
XLeftDate215105No
XLeftDate3261511No
XLeftDate41026-16Yes
XLeftDate52026-6No
XLeftDate635269No
XLeftDate71035-25Yes

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

Respected Advisor
Posts: 4,606

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

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

PG
Ask a Question
Discussion stats
  • 1 reply
  • 141 views
  • 2 likes
  • 2 in conversation