Selecting from a Group

[BCNAV]

Flights are assigned a UNIQUE numeric flt_biz_uid.  Each flight can have multiple messages associated with it. These messages are then grouped into 3 numeric types that are either 1 or 0 (true or not). Here is an example:

flt_biz_uid    msg_biz_uid    edm_datalink    gander_datalink    gander_voice

1                  22         1               0                  0

1                  23         0               0                  1

1                  26         0               0                  0

2                  26         0               0                  0

2                  22         0               0                  0

2                  23         0               0                  0

Supposed I want to pick flights that have edm_datalink=0 AND gander_datalink=0 AND gander_voice=0, but only if that particular flight has no messages that ever contained a edm_datalink=1 or a gander_datalink=1 or a gander_voice=1

In the example above, flt_biz_uid of 1 would NOT be included since its flight contains messages that have a 1 somewhere in edm_datalink, gander_datalink, or a gander_voice.

Meanwhile, flt_biz_uid 2 would be selected as there are 0s in all of its flight history for edm_datalink, gander_datalink, and gander_voice.

Can this be done easily?

Essentially this is "select flt_biz_uid when edm_datalink=0 AND gander_datalink=0 AND gander_voice=0 as long as the history of that flt_biz_uid has ALWAYS had edm_datalink=0 AND gander_datalink=0 AND gander_voice=0"

thx!

[novinosrin]

---

@BCNAV wrote:

Essentially this is "select flt_biz_uid when edm_datalink=0 AND gander_datalink=0 AND gander_voice=0 as long as the history of that flt_biz_uid has ALWAYS had edm_datalink=0 AND gander_datalink=0 AND gander_voice=0"

thx!

---

Do you mean this?

data have;
input flt_biz_uid    msg_biz_uid    edm_datalink    gander_datalink    gander_voice ;
cards;
1                  22         1               0                  0
1                  23         0               0                  1
1                  26         0               0                  0
2                  26         0               0                  0
2                  22         0               0                  0
2                  23         0               0                  0
;

proc sql;
create table want(drop=m) as 
select *,max(max(edm_datalink ,   gander_datalink ,   gander_voice)) as m
from have
group by flt_biz_uid
having m=0;
quit;

[novinosrin · Accepted solution]

Another way to write the same query

proc sql;
create table want as 
select *
from have
group by flt_biz_uid
having max(max(edm_datalink ,   gander_datalink ,   gander_voice))=0;
quit;

[novinosrin]

Or Datastep

data want;
do until(last.flt_biz_uid);
set have;
by flt_biz_uid;
m=max(edm_datalink ,   gander_datalink ,   gander_voice);
m1=max(m1,m);
end;
do until(last.flt_biz_uid);
set have;
by flt_biz_uid;
if not m1 then output;
end;
drop m:;
run;

[BCNAV]

awesome!