BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lmyers2
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
japelin
Rhodochrosite | Level 12

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;

 

 

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Rhodochrosite | Level 12

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
Obsidian | Level 7

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 942 views
  • 0 likes
  • 3 in conversation