Quartz | Level 8

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

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.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

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

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);
by ID;
if flag1='Y' then c=c+1;
end;
do until(last.ID);
by ID;
if _N_=c then output;
end;
run;``````
9 REPLIES 9
Tourmaline | Level 20

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

One way

``````proc sql;
create table want as
group by ID
having sum(flag1='Y')=count(ID);
quit;``````
Tourmaline | Level 20

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

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);
by ID;
if flag1='Y' then c=c+1;
end;
do until(last.ID);
by ID;
if _N_=c then output;
end;
run;``````
Tourmaline | Level 20

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

``````

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 *
group by id
having count(distinct flag1)=1 and flag1='Y';
quit;
``````
Super User

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

Just for fun.

``````
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;``````
Quartz | Level 8

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

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

Super User

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

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

Here is base on what I know.

``````
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
group by id;
quit;``````
Tourmaline | Level 20

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

"what I know."

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

Super User

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

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 .

Tourmaline | Level 20

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

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.

Discussion stats
• 9 replies
• 714 views
• 0 likes
• 4 in conversation