topic Re: Create flag for an ID if at least one of the results is a certain value in SAS Data Management

Create flag for an ID if at least one of the results is a certain value

KPCklebspn — Wed, 14 Nov 2018 19:23:12 GMT

Dataset

ID Result

1 pos

1 neg

1 pos

1 neg

2 neg

I want to create a flag for an ID if at least one or more of the Result values for that ID is pos, so I want it to look like

ID Result Flag

1 pos. 1

1 neg. 1

1 pos. 1

1 neg. 1

2 neg 0

How do I do this?

Re: Create flag for an ID if at least one of the results is a certain value

novinosrin — Wed, 14 Nov 2018 19:28:38 GMT

data have;
input ID   Result $;
cards;
1   pos
1   neg
1   pos
1   neg
2   neg
2   neg
2   neg
;

proc sql;
create table want as
select *,sum(upcase(result)='POS')>0 as flag
from have
group by id;
quit;

Re: Create flag for an ID if at least one of the results is a certain value

kiranv_ — Wed, 14 Nov 2018 19:50:50 GMT

one more way

proc sql;
create table want as
select *,
max(case when result ='pos' then 1 else 0 end) as flag
from have
group by id;
quit;

Re: Create flag for an ID if at least one of the results is a certain value

novinosrin — Wed, 14 Nov 2018 20:14:51 GMT

data have;
input ID   Result $;
cards;
1   pos
1   neg
1   pos
1   neg
2   neg
2   neg
2   neg
;

data want;
merge have(in=in1) have(where=(upcase(result)='POS') in=in2);
by id;
flag=in1 and in2;
run;

flag=in2; alone will do as a matter of fact

data want1;
merge have(in=in1) have(where=(upcase(result)='POS') in=in2);
by id;
flag=in2;
run;

Re: Create flag for an ID if at least one of the results is a certain value

KPCklebspn — Wed, 14 Nov 2018 20:42:47 GMT

Thx so much - worked

Re: Create flag for an ID if at least one of the results is a certain value

KPCklebspn — Thu, 19 Dec 2019 02:18:18 GMT

this works beautifully. would you be able to explain what max (case means pls?