Flag if there is one of each of 3 pre-specified values of a variable within a by group

[KPCklebspn]

Hi all,

Dataset below:

ID   sample

p1      a

p1      b

p1      a

p1       c

p2      a

p2      b

p3     a        

p3     c        

p3     b       

I want it to look as follows, where flag =1 if within a by group (ID), there is at least one observation within the sample variable of each of a, b, and c

ID   sample  flag

p1      a        1

p1      b        1

p1      a        1

p1       c       1

p2      a        0

p2      b        0

p3     a         1

p3     c         1

p3     b        1

Is there a way to do this? Maybe with proc sql?

Thank yoU!

[novinosrin]

HI @KPCklebspn 

data have;
input (ID   sample) ($);
cards;
p1      a
p1      b
p1      a
p1       c
p2      a
p2      b
p3     a        
p3     c        
p3     b       
;

proc sql;
create table want as
select *,count(distinct ifc(sample in ('a','b','c'),sample,' '))=3 as Flag
from have
group by id;
quit;

proc print noobs;run;

[biopharma]

@novinosrin - elegant!

[novinosrin]

Thank you @biopharma 

[PaigeMiller]

proc freq data=have;
    table id*sample/out=_counts_ noprint;
run;
proc transpose data=_counts_ out=_counts_t_;
	var count;
	by id;
	id sample;
run;
data want;
	merge have _counts_t_(drop=_:);
	by id;
	if a>=1 and b>=1 and c>=1 then flag=1;
	else flag=0;
run;

Assumes your input data set is sorted by ID.

[yabwon]

Hi,

Double DoW-loop:

data have;
input (ID sample) ($);
cards;
p1 a
p1 b
p1 a
p1 c
p2 a
p2 b
p3 a
p3 c
p3 b
;
run;

data want;
  list = 'a b c'; 
  drop list;
  do _N_ = 1 by 1 until(last.ID);
    set have;
    by ID;
    list = TRANWRD(list, strip(sample), " ");
  end;

  flag = (list = " ");

  do _N_ = 1 to _N_;
    set have;
    output;
  end;
run;

All the best

Bart

[biopharma]

Can be done in a data step but needs to be read twice.

data have ;
input ID $   sample $ ;
cards;
p1      a
p1      b
p1      a
p1      c
p2      a
p2      b
p3      a        
p3      c        
p3      b       
;

proc format ;
   invalue samp
     "a" = 1
     "b" = 2
     "c" = 3 
   ;
run ;

data want ;
   array nums (*) a b c ;
   drop a b c ;
   do until (last.id) ;
      set have ;
      by id ;
      nums(input(sample,samp.))=1 ;
   end ;
   flag = sum(of nums(*))=3 ;
   do until (last.id) ;
      set have ;
      by id ;
      output ;
   end ; 
run ;

[novinosrin]

data have;
input (ID   sample) ($);
cards;
p1      a
p1      b
p1      a
p1       c
p2      a
p2      b
p3     a        
p3     c        
p3     b       
;

data want ;
 if _n_=1 then do;
  dcl hash H () ;
  h.definekey  ("sample") ;
  h.definedone () ;
 end;
 do _n_=1 by 1 until(last.id);
  set have;
  by id;
  if h.check()=0 or sample not in ('a','b','c') then continue;
  h.add();
 end;
 do _n_=1 to _n_;
  set have;
  Flag=h.num_items=3;
  output;
 end;
 h.clear();
run;

[yabwon]

DoW-loop + hash = perfect match.

Maybe just `if sample in ('a','b','c') then h.replace();` ?

Bart

[novinosrin]

"Maybe just `if sample in ('a','b','c') then h.replace();` ? " - I agree Sir!