Creating subset of observations based on a varaible

[PaigeMiller]

---

@saslovethemost wrote:

for the 1 record, I want the bottom one, for the two records, I want to have bottom two records.

---

This disagrees with your earlier answer to my question about which two records you want to keep, in which you said you wanted to keep NEAL and DAVE. But the bottom two records are DAVE and PHIL. So, I still don't know what the right answer is that you want from the code.

[Tom]

Sounds like you want to group the data by LN_NO variable.

Then keep either the first or the first two observations in that group based on the value of LN_STATUS and LN_EXCEP_STAT.

Here is a method using the value of LN_STATUS and LN_EXCEP_STAT on the FIRST observation in the group.

data want ;
  set have ;
  by LN_NO;
  retain row maxrow;
  if first.ln_no then do;
      row=1;
      if ln_excep_stat in ('1' '5') and ln_stat in ('1' '5') then maxrow=1;
      else maxrow=2;
  end;
  else row+1;
  if row <= maxrow;
run;
   
     

[saslovethemost]

Thanks Tom for the reply, sorry I misguided you with my code, but I do not want the no of obs values, I want the actual data mentioned in the datalines but subset of it. For a ln_no if there are more than 2 records, I want to retain last 2 records for ln_stat and ln_excep_status '2' or '3' or '4' . same way, I want to retain 1 record if ln_stat and ln_excep_status '1' or '5'  if there are more than 1 record. If there is only one record, I want to retain that one record. sorry for all the confussion.

[Tom]

Your specs are still missing a number of details.

• Does the same id always have the same values of the two status variables?  If not then how does you logic for how many observations to keep change?
• Do you want only one row kept if BOTH status variables are 1 or 5?  Or if EITHER status variable is 1 or 5?
• If the order is important why not just sort the data in descending order do that you can pick the first one or two instead of the last one or two.

It is tradition to use WANT as the output dataset name and HAVE as the input dataset name in examples.  Replace those with the names of your actual data.

Don't make your life harder by trying to combine the step that converts the raw text into a dataset with the logic to subset it.  

If the data is really large (and what is considered large changes as computers get more powerful) you could use a data step view to read the data so that there is actually only one pass thru the data.

If you did want the LAST one or two then just change the logic a little.  You can use what is call DOW loops to make that easier.  The first DO loop counts the number of rows in the group.  Then second reads the group in again and writes out which records you want.

data want ;
  do maxrow = 1 by 1 until (last. LN_NO);
    set have ;
    by LN_NO;
  end;
  if ln_excep_stat in ('1' '5') and ln_stat in ('1' '5') then keeprows=1;
  else keeprows=2;
  do row = 1 by 1 until (last. LN_NO);
    set have ;
    by LN_NO;
    if row > (maxrow - keeprows) then output;
  end;
run;