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;
proc sql;
select name,case when sum(score=1)>=1 then 'yes' else 'no' end as Yes_No from have group by name;
quit;
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
proc sql;
select name,case when sum(score=1)>=1 then 'yes' else 'no' end as Yes_No from have group by name;
quit;
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;
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;
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;
Good job! Looks like SQL is the most powerful way in this case. Thanks for all.
Z
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.