topic Re: keeping matched observations in SAS Programming

keeping matched observations

lillymaginta — Sat, 23 May 2020 23:36:41 GMT

DATA HAVE;
INPUT patientid drug  class matchto;
datalines;
1  1  a   4
2  1  a   5
3  1   b  6
4  2   .   1
5  2  .    2
6  2  .    3
;
run;

I am trying to create a data subset where drug=1 and class =a but I want to keep the matching observations from drug=2

output dataset

1  1  a   4
2  1  a   5

4  2   .   1
5  2  .    2

Re: keeping matched observations

RichardDeVen — Sun, 24 May 2020 11:19:43 GMT

You will need to track the MATCHTO values in some manner for later comparison. Tracking possibilities:

array
hash
query

Examples (edited to test drug=2 before checking matchto):

DATA HAVE;
INPUT patientid drug class $ matchto;
datalines;
1  1  a   4
2  1  a   5
3  1  b   6
4  2  .   1
5  2  .   2
6  2  .   3
;

* array.  only works if all matchtos come after drug=1 class='a';
data want;
  array match[100] _temporary_;
  set have;

  if drug=1 and class='a' then do;
    top+1;
    match[top] = matchto;                                    /* tracking */
    output;                                                  /* output original */
  end;

if drug=2 then do;
  do ix = 1 to top while (match[ix] ne patientid);
  end;

  if match[ix] eq patientid then output;                     /* output match */
end;

  drop ix top;
run;

* hash. load lookups from same as SET. does not matter where matches occur;
data want;
  if _n_ = 1 then do;
    declare hash lookup(dataset:'have(where=(drug=1 and class="a"))');
    lookup.defineKey('matchto');
    lookup.defineData('patientid');
    lookup.defineDone();                                       /* load tracking */
  end;

  set have;

  if (drug=1 and class='a') or (drug=2 and lookup.check(key:patientid) eq 0);
                                                                   /* implicit output, either original or match */
run;

* existential query for selecting matched rows;
proc sql;
create table want as
select * from have as outer
where drug=1 and class='a'                                   /* select original */
or exists ( select * from have as inner                      /* select a match via existential correlated sub-query */
            where inner.drug=1 and inner.class='a'
            and inner.matchto = outer.patientid
            and inner.drug = 2 
          )
;

* another version using SQL;
proc sql;
  create table want as
  select A.* from have A where A.drug=1 and A.class='a'
  union
  select B.* from have B where 
  exists(select * from have where drug=1 and class='a' and matchto=B.patientid and B.drug=2)
  ;

Re: keeping matched observations

ErikLund_Jensen — Sun, 24 May 2020 09:31:11 GMT

Hi @lillymaginta

First select the wanted observations (drug=1 and class =a).

Then select observations where matchto matches observations from the first select.

Finally append:

DATA HAVE;
INPUT patientid drug class$ matchto;
datalines;
1  1  a   4
2  1  a   5
3  1   b  6
4  2   .   1
5  2  .    2
6  2  .    3
;
run; 

proc sql;
	create table a as select * from have
	where drug = 1 and class = 'a';
quit;
proc sql;
	create table b as select * from have
	where matchto in (select patientid from a);
quit;
data want; set a b;
run;

Re: keeping matched observations

Ksharp — Sun, 24 May 2020 11:04:57 GMT

DATA HAVE;
INPUT patientid drug  class $ matchto;
datalines;
1  1  a   4
2  1  a   5
3  1   b  6
4  2   .   1
5  2  .    2
6  2  .    3
;
run; 
data temp;
 set have;
 by drug;
 if first.drug then n=0;
 n+1;
run;
proc sql;
create table want as
select * from temp
 where n in (select distinct n from temp where drug=1 and class='a');
quit;