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!
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;
Thank you @biopharma
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.
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
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 ;
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;
"Maybe just `if sample in ('a','b','c') then h.replace();` ? " - I agree Sir!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.