topic Re: How to flag a group that has at least one particular value in SAS Programming

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

imcbczm — Thu, 10 Apr 2014 03:38:48 GMT

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!

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

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;

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

RichardinOz — Thu, 10 Apr 2014 04:09:22 GMT

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

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

slchen — Thu, 10 Apr 2014 04:20:33 GMT

proc sql;

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

quit;

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

Chrishi — Thu, 10 Apr 2014 06:41:21 GMT

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;

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

SKK — Thu, 10 Apr 2014 07:13:46 GMT

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;

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

pradeepalankar — Thu, 10 Apr 2014 07:41:44 GMT

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;

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

imcbczm — Thu, 10 Apr 2014 12:41:20 GMT

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