topic Re: extract data before and after a string in a dataset in SAS Programming

extract data before and after a string in a dataset

noda6003 — Wed, 15 Dec 2021 09:51:48 GMT

I want to extract data based on a string and also need one record above and one below.

Input data is like below

S.No.	visit	value
101	V1	3
101	V2	54
101	Terminal	566
101	V3	67
102	V1	88
102	Terminal	99
103	V1	54
103	V2	566
103	V3	67
103	Terminal	22
103	V4	11

Output should be like

S.No.	visit	value
101	V2	54
101	Terminal	566
101	V3	67
102	V1	88
102	Terminal	99
103	V3	67
103	Terminal	22
103	V4	11

Can anyone please help me on how to get it

Re: extract data before and after a string in a dataset

Kurt_Bremser — Wed, 15 Dec 2021 10:21:37 GMT

data want;
merge
  have
  have (
    firstobs=2
    keep=s_no visit
    rename=(s_no=_sno visit = _visit)
  )
;
if
  visit = 'Terminal'
  or
  lag(s_no) = s_no and lag(visit) = 'Terminal'
  or
  _sno = s_no and _visit = 'Terminal'
;
run;

Untested; for tested code, supply example data in a working data step with valid (V7) SAS names.

Re: extract data before and after a string in a dataset

andreas_lds — Thu, 16 Dec 2021 12:07:37 GMT

Here is a solution using a hash-object:

data numbered;
   set work.have;

   pos = _n_;
run;

data want;
   set work.numbered;

   if _n_ = 1 then do;
      declare hash h(dataset: 'work.numbered(where=(visit="Terminal"))');
      h.defineKey('sno', 'pos');
      h.defineDone();
   end;

   if visit = 'Terminal' or (h.check(key: sno, key: pos-1) = 0) or (h.check(key: sno, key: pos+1) = 0);
run;

Re: extract data before and after a string in a dataset

AllanBowe — Thu, 16 Dec 2021 22:21:02 GMT

This macro can also be used to retrieve previous observations - https://core.sasjs.io/mp__prevobs_8sas.html