title: SAS EG (SQL) deleting rows where max value in one column
body: <p>I need to delete all the rows with a max value of <code>duty_perd_id</code> where the <code>rotn_prng_nbr</code> and <code>empl_nbr</code> are the same (not the same to each other, but the max where of all of the rows where those two remain constant). From the table below it should delete rows 3,7 and 9.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>rotn_prng_nbr</th>
  <th>empl_nbr</th>
  <th>duty_perd_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>B93</td>
  <td>12</td>
  <td>1</td>
  </tr>
  <tr>
  <td>B93</td>
  <td>12</td>
  <td>2</td>
  </tr>
  <tr>
  <td>B93</td>
  <td>12</td>
  <td>3</td>
  </tr>
  <tr>
  <td>B21</td>
  <td>12</td>
  <td>1</td>
  </tr>
  <tr>
  <td>B21</td>
  <td>12</td>
  <td>2</td>
  </tr>
  <tr>
  <td>B21</td>
  <td>12</td>
  <td>3</td>
  </tr>
  <tr>
  <td>B21</td>
  <td>12</td>
  <td>4</td>
  </tr>
  <tr>
  <td>B21</td>
  <td>18</td>
  <td>1</td>
  </tr>
  <tr>
  <td>B21</td>
  <td>18</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>using SAS EG. Right now all have is below:</p>
  <p>Option 1:</p>
  <pre><code> create table middle_legs as
      select t.*
      from actual_flt_leg as t
      where t.duty_perd_id &lt; (select max(t2.duty_perd_id)
                              from actual_flt_leg as t2
                              where t2.rotn_prng_nbr = t.rotn_prng_nbr and
                                    t2.empl_nbr = t.empl_nbr
                             );
  </code></pre>
  <p>this works exactly as intended, but is incredibly slow. The other thought that I had but couldnt quite finish was as follows.</p>
  <p>Option 2:</p>
  <pre><code>create table last_duty_day as
  Select * from actual_flt_leg 
  inner join (
      select actual_flt_leg.Rotn_Prng_Nbr,actual_flt_leg.empl_nbr, max(duty_perd_id) as last_duty
      from actual_flt_leg
      group by actual_flt_leg.Rotn_Prng_Nbr, actual_flt_leg.empl_nbr
  ) maxtable on 
      actual_flt_leg.Rotn_Prng_Nbr = maxtable.Rotn_Prng_Nbr
      and actual_flt_leg.empl_Nbr = maxtable.empl_Nbr
      and actual_flt_leg.duty_perd_id = maxtable.last_duty;
  </code></pre>
  <p>option 2 finds all the highest <code>duty_perd_id</code> for the given pair, and I was wondering if there was any &quot;reverse join&quot; that could only show the rows from the original table that do not match this new table i created in option 2.</p>
  <p>If there is a way to make option 1 faster, finish option 2, or anything else i cant think of id appreciate it. Thanks!</p>