choosing the first row satisfying another column in a long file

[lmyers2]

Hello,

I have a long file of oxygen which contains many timestamped rows of oxygen per patient.  I'm trying to generate a proc sql statement that chooses the "Oxygen" value where the Tier column reaches 3 for the first time. I've already sorted the data. In this case it would be "HF" for patient 1 and "CPAP" for patient 2. If anyone has code that might work, would love to use it. Thanks!

Patient  Oxygen  Tier   Hour from admission

1            NC          1      3

1            HF           3      6

1            V             3       12

2            CPAP      3       2

[ballardw]

SQL really doesn't have a concept of internal data order.

A data step would be a better choice in general if you want to process data in a specific sequence.

data example;
  input Patient $  Oxygen $  Tier    Hour;
datalines;
1            NC          1      3
1            HF           3      6
1            V             3       12
2            CPAP      3       2
;

data want;
   set example;
   by patient;
   retain firsttime;
   if first.patient then firsttime=0;
   if firsttime=0 and tier ge 3 then do;
      output;
      firsttime=1;
   end;
   drop firsttime;
run;

BY group processing creates special temporary variables that indicate whether a record is the first or last of the group. These are referenced using First.variablename or Last.variablename. The values are numeric 1/0 which SAS treats as true or false and so can be used in IF statements as shown.

Retain keeps the value of a variable from one iteration of the data step to the next. So we see a flag variable to 0 on the first record of each patient. Then when we get to the tier greater than or equal to 3 it outputs the record and sets the flag so that following records can tell they are not the first time.

You didn't provide much example data about such things as what if the Tier is never exactly 3? So I assumed that if the value is greater than 3 but 3 was skipped for some reason you want to know.

The above assumes the data is sorted by patient and an appropriate variable for 'time'.

[japelin · Accepted solution]

If you insist on SQL, you will have to go through a tedious process. This is because there is no efficient function to get the first one.

data have;
  input Patient Oxygen $ Tier Hourfromadmission;
datalines;
1 NC 1 3
1 HF 3 6
1 V  3 12
2 CPAP 3 2
;
run;

proc sql;
  create table tmp as
  select patient, oxygen, MONOTONIC() as ID from have
  where tier=3
  ;
  select oxygen from tmp as tmp1
  where tmp1.id=(select min(id) as id2 from tmp group by patient having tmp1.id=tmp.id)
  ;
quit;

For data steps, it is easy to use by and first.

data want;
  set have;
  by patient;
  where tier=3;
  if first.patient;
run;

[lmyers2]

Thanks, that simple code worked!  As a follow up question, how would you use the data step to find a specific value after a certain criteria is met. I have a long file that has timestamps that is sorted (sample below). I'm looking to find how many patients get "CPAP" any time after a row of HF. The answer here would be patient 1 and 2 so 2 of the 3 patients.

Patient     Tier     oxygen         Timestamp

1               1            NC                 Time

1               3            HF                  Time

1               3            CPAP             Time

2               3            HF                  Time

2               3            CPAP             Time

3               1            NC                  Time

[japelin]

If you have another question, please create another Topic.
Otherwise, later visitors will not know which answer to which question is the Solution.