topic Re: Getting a subset of a dataset and including previous record for each in SAS Programming

Getting a subset of a dataset and including previous record for each

RandoDando — Wed, 15 Jan 2025 19:15:10 GMT

I want to create a subset from a data set to find records which meet a certain set of criteria (e.g. fieldA > 1,000 AND fieldB <1, etc...) AND include the record prior to those for each according to how the dataset is currently sorted. How do I do that? In other words, if record number 100 fits the criteria, I want to include record 99, etc... I want to retain the order of the data.

Re: Getting a subset of a dataset and including previous record for each

data_null__ — Wed, 15 Jan 2025 19:25:27 GMT

data test;
   set sashelp.class(keep=age);
   if age eq 12 then do;
      grp = _n_;
      do point=_n_-1 to _n_;
         obs = point;
         set sashelp.class point=point;
         output;
         end;
      end;
   run;
proc print;
   run;

Re: Getting a subset of a dataset and including previous record for each

RandoDando — Wed, 15 Jan 2025 19:39:46 GMT

That did it. Thanks.

Re: Getting a subset of a dataset and including previous record for each

RandoDando — Wed, 15 Jan 2025 19:44:34 GMT

That seemed to work and will work for the time being, but is there a way to modify it to avoid records being duplicated? Seems as though if a record matches the criteria AND is prior to a record which does it shows up twice.

Re: Getting a subset of a dataset and including previous record for each

Tom — Wed, 15 Jan 2025 21:21:41 GMT

@RandoDando wrote:

That seemed to work and will work for the time being, but is there a way to modify it to avoid records being duplicated? Seems as though if a record matches the criteria AND is prior to a record which does it shows up twice.

In that case it might just be easier to do it two steps. One to flag the records and then a second step to make the subset. You can use one of the various methods for "looking ahead" to see if the next record matched the criteria.

data make_flag;
  set sashelp.class;
  match = (age=12);
run;

data want ;
  obsno+1;
  merge make_flag make_flag(firstobs=2 keep=match rename=(match=next_match));
  * DO NOT USE A BY STATEMENT ;
  if match or next_match;
run;

proc print;
run;

If the criteria depends only a few variables then just use the look ahead trick on those variables and you can do it in one pass.

data want ;
  obsno+1;
  merge sashelp.class sashelp.class(firstobs=2 keep=age rename=(age=next_age));
  * DO NOT USE A BY STATEMENT ;
  if (age=12) or (next_age=12);
run;

Re: Getting a subset of a dataset and including previous record for each

simonbun — Wed, 15 Jan 2025 22:34:27 GMT

You could keep track of whether the previous record has been output.

data test;
  set sashelp.class;
  if age eq 12 then do;
    if prevBeenOutput then output;
    else do;
      do point = max(_n_-1, 1) to _n_;
        set sashelp.class point=point;
        output;
      end;
      prevBeenOutput = 1;
    end;
  end;
  else prevBeenOutput = 0;

  retain prevBeenOutput;
  drop prevBeenOutput;
run;