topic how to include records which has "Y" for all ID's. in SAS Programming

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

noda6003 — Mon, 02 Sep 2019 13:30:49 GMT

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

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

PeterClemmensen — Mon, 02 Sep 2019 13:32:58 GMT

One way

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

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

PeterClemmensen — Mon, 02 Sep 2019 13:40:38 GMT

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;

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

novinosrin — Mon, 02 Sep 2019 13:40:40 GMT



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;

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

Ksharp — Tue, 03 Sep 2019 12:49:59 GMT

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;

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

noda6003 — Wed, 04 Sep 2019 14:25:58 GMT

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

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

Ksharp — Thu, 05 Sep 2019 11:15:48 GMT

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;

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

novinosrin — Thu, 05 Sep 2019 11:30:05 GMT

"what I know."

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

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

Ksharp — Thu, 05 Sep 2019 11:52:00 GMT

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 .

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

novinosrin — Thu, 05 Sep 2019 12:00:07 GMT

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.