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

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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

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;


Accepted Solutions
Solution
‎04-10-2014 12:20 AM
Super Contributor
Posts: 275

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

proc sql;

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

  quit;

View solution in original post


All Replies
Super Contributor
Posts: 644

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

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

Solution
‎04-10-2014 12:20 AM
Super Contributor
Posts: 275

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

proc sql;

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

  quit;

Contributor
Posts: 29

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

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;

Contributor SKK
Contributor
Posts: 35

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

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;

Frequent Contributor
Posts: 106

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

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;

Contributor
Posts: 40

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

Posted in reply to pradeepalankar

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

Z

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 313 views
  • 6 likes
  • 6 in conversation