Select where based on multiple columns

adiezmann

I'm not sure if there's a good way to do what I'm hoping to do, but I have the following situation:

I have a data file which is the result of collecting measurements every second. There's a timestamp column, a status column, and then all my data columns.

There's three different possible entries in the status column, "RAMP", "WAIT", and "READ". The general ordering of data by time results in, say, about 20 rows of "RAMP" followed by 85 rows of "WAIT" followed by 9 rows of "READ", which then repeats a dozen times. My regions of interest, which I would like to select, are the last 30 rows within each section labeled as "WAIT".

Obviously, it's extremely simple to select all "WAIT" rows, or the last 30 rows of the entire file, but I can't figure out how to select only the last 30 of each section.

Is there a good way of solving this?

astounding

You posted this in the JMP forum, so I'm not sure if this helps or not.  Using base SAS, it's a relatively short program if you use the right tools:

data last_30_waits;

   group_num + 1;

   n_waits=0;

   do until (last.status and status='WAIT');

      set measurements (keep=status);

      by status notsorted;

      if status='WAIT' then n_waits + 1;

   end;

   ** At this point, group_num identifies a set of WAITs, and n_waits shows how many records are in the group;

   n_found=0;

   do until (last.status and status='WAIT');

      set measurements;

      by status notsorted;

      if status='WAIT' then do;

         n_found + 1;

         if n_found >= n_waits - 29 then output;

      end;

   end;

   drop n_found;

run;

Good luck.