topic Selecting from a Group in SAS Programming

Selecting from a Group

BCNAV — Thu, 28 Feb 2019 15:58:09 GMT

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!

Re: Selecting from a Group

novinosrin — Thu, 28 Feb 2019 16:09:14 GMT

@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;

Re: Selecting from a Group

novinosrin — Thu, 28 Feb 2019 16:25:53 GMT

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;

Re: Selecting from a Group

novinosrin — Thu, 28 Feb 2019 16:34:56 GMT

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;

Re: Selecting from a Group

BCNAV — Thu, 28 Feb 2019 16:46:01 GMT

awesome!