how to include records which has "Y" for all ID's.

[noda6003]

I want to filter on records which has "Y" across id's and exclude id's which has "Y" for some id and "N" or missing for the rest.

data WORK.ADS;
infile datalines dsd truncover;
input ID:BEST12. ecs:BEST12. par:$8. flag1:$1.;
datalines4;
12,1,par1,Y
12,2,par1,Y
12,3,par1,Y
12,4,par2,Y
13,1,par1,
13,2,par1,Y
13,3,par1,N
13,4,par2,Y
14,1,par1,N
;;;;

i would only want observations as it has "Y" for all  id, ecs, par

12,1,par1,Y
12,2,par1,Y
12,3,par1,Y

12,4,par2,Y

any help on how to get them

[PeterClemmensen]

One way

proc sql;
    create table want as
    select * from WORK.ADS
    group by ID
    having sum(flag1='Y')=count(ID);
quit;

[PeterClemmensen · Accepted solution]

Here is a data step approach that assumes that the data is sorted by ID

data want(drop=c);
    c=0;
    do _N_=1 by 1 until (last.ID);
        set ads;
        by ID;
        if flag1='Y' then c=c+1;
    end;
    do until(last.ID);
        set ads;
        by ID;
        if _N_=c then output;
    end;
run;

[novinosrin]

data WORK.ADS;
infile datalines dsd truncover;
input ID:BEST12. ecs:BEST12. par:$8. flag1:$1.;
datalines4;
12,1,par1,Y
12,2,par1,Y
12,3,par1,Y
12,4,par2,Y
13,1,par1,
13,2,par1,Y
13,3,par1,N
13,4,par2,Y
14,1,par1,N
;;;;

proc sql;
create table want as
select *
from ads
group by id
having count(distinct flag1)=1 and flag1='Y';
quit;

[Ksharp]

Just for fun.

data WORK.ADS;
infile datalines dsd truncover;
input ID:BEST12. ecs:BEST12. par:$8. flag1:$1.;
datalines4;
12,1,par1,Y
12,2,par1,Y
12,3,par1,Y
12,4,par2,Y
13,1,par1,
13,2,par1,Y
13,3,par1,N
13,4,par2,Y
14,1,par1,N
;;;;

proc sql;
create table want as
select *
 from ads where id not in (
select distinct id from ads where flag1 ne 'Y');
quit;

[noda6003]

Thanks, can i update it with condition adding if ecs=1 has "Y" and the rest of ecs for that ID has atleast one  "Y" then i need all id's with these cases instead of having all "Y". Can it be done on same step 

[Ksharp]

Your question is quite ambiguous . Can you post the outpu you need.

Here is base on what I know.

data WORK.ADS;
infile datalines dsd truncover;
input ID:BEST12. ecs:BEST12. par:$8. flag1:$1.;
datalines4;
12,1,par1,Y
12,2,par1,Y
12,3,par1,Y
12,4,par2,Y
13,1,par1,
13,2,par1,Y
13,3,par1,N
13,4,par2,Y
14,1,par1,N
;;;;

proc sql;
select *,max(flag1) as new_flag
 from ads
  group by id;
quit;

[novinosrin]

"what I know."

@Ksharp   There isn't anything that you don't know. I found that funny. 

[Ksharp]

Sorry. Should be what I understand. 

English is not my native language ,so there is some glitch in my post as @data_null__  pointed out .

[novinosrin]

Hey come on!, What i meant is. Let me clarify. Is there anything does Xia Keshan doesn't know? The world will end. As I have said many times, my mother is your biggest fan. 🙂

PS i didn't mean divert the attention of the focus of the topic. So my apologies to you and OP for that.