topic Re: Flag if there is one of each of 3 pre-specified values of a variable within a by group in SAS Programming

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

KPCklebspn — Fri, 15 May 2020 16:30:00 GMT

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!

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

novinosrin — Fri, 15 May 2020 16:37:03 GMT

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;

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

PaigeMiller — Fri, 15 May 2020 16:38:00 GMT

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.

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

yabwon — Fri, 15 May 2020 16:54:02 GMT

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

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

novinosrin — Fri, 15 May 2020 17:36:51 GMT


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;

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

biopharma — Fri, 15 May 2020 17:39:47 GMT

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 ;

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

biopharma — Fri, 15 May 2020 17:41:54 GMT

@novinosrin - elegant!

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

novinosrin — Fri, 15 May 2020 17:53:53 GMT

Thank you @biopharma

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

yabwon — Fri, 15 May 2020 17:54:49 GMT

DoW-loop + hash = perfect match.

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

Bart

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

novinosrin — Fri, 15 May 2020 17:56:13 GMT

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