How to flag a group that has at least one particular value

[imcbczm]

hi,

I want to flag a group which has at least one particular value. In this example, I want to flag a name as "Yes" if it has at least one score=1, otherwise flag the name as "No". What is the best way?

Thanks!

Z

==================================

data have;

input name $ score;

datalines;

a 1

a 2

a 3

a .

a 1

b 2

b .

b 4

c .

c 1

c 4

;

run;

data want;

input name $ YesNo $;

datalines;

a yes

b no

c yes

;

run;

[RichardinOz]

Proc SQL ;

     Create table want as

          Select name

               ,     case

                         when min = 1 then 'yes'

                         else 'no'

                    end as YesNo

          From

               (Select name

                    ,     min (score) as min

               From have

               Group by name

               )

          ;

(Untested)

Richard

[slchen · Accepted solution]

proc sql;

  select name,case when sum(score=1)>=1 then 'yes' else 'no' end as Yes_No from have group by name;

  quit;

[Chrishi]

The above appreoaches are based on sql so i am posting this code which is developed using base sas.

proc sort data=have;

  by name score;

run;

data want;

set have;

by name score;

where score > 0;

if first.name and score=1 then yesno='YES';

if first.name and score ne 1 then yesno='NO';

if yesno ne '';

run;

[SKK]

Another approach with SQL:

proc sql;

create table want as

select  name,

case when max(flag) = 1 then "yes" else "no" end as yesno

from (select name, ifn(score=1,1,0) as flag from have)  group by name;

quit;

[pradeepalankar]

data have;

input name $ score;

datalines;

a 1

a 2

a 3

a .

a 1

b 2

b .

b 4

c .

c 1

c 4

;

run;

proc sql;

select distinct name, case  when name in (select distinct name from have where score=1) then 'yes'

else 'no' end as yesno  from have;

quit;

[imcbczm]

Good job! Looks like SQL is the most powerful way in this case. Thanks for all.

Z