BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
imcbczm
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
slchen
Lapis Lazuli | Level 10

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

6 REPLIES 6
RichardinOz
Quartz | Level 8

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Calcite | Level 5 SKK
Calcite | Level 5

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
Obsidian | Level 7

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
Obsidian | Level 7

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

Z

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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