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!
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;
@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;
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;
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;
awesome!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.