topic Re: Select Data with Same ID Based on Conditions in SAS Programming

Select Data with Same ID Based on Conditions

BCNAV — Wed, 24 Oct 2018 15:51:25 GMT

When aircraft fly in our airspace they are given a unique numeric ID called FLT_BIZ_UID. As messages are sent between the aircraft and ATC, they are stored (items stored include, the message, aircraft type and a bunch of other things). Every time there is a message by that specific flight a new FLT_BIZ_UID record is made (with the same numeric number of course as the flight is continuing on).

For example:

FLT_BIZ_UID 15 variables across EDMONTON_DATALINK GANDER_DATALINK GANDER_VOICE

123456 1 0 0

333333 1 0 0

333333 1 0 1

So as you can see, there are multiple records for FLT_BIZ_UID based on the number of times the aircraft messages ATC

I would like to be able to generate a subset of this data.

I want all FLT_BIZ_UID observations that are all EDMONTON_DATALINK=1 GANDER_DATALINK=0 and GANDER_VOICE=0

So above, all 123456 records would be kept, while none of 333333 would be kept because the last observation of the group containing 333333 in the FLT_BIZ_UID has a GANDER_VOICE=1

Not sure how to approach this

thanks

Re: Select Data with Same ID Based on Conditions

SuryaKiran — Wed, 24 Oct 2018 16:09:58 GMT

A sub-query might help. Create a query for finding all the FLT_BIZ_UID that are not in the condition your looking for and use that as a sub-query to eliminate those records.

Something like this (untested):

proc sql;
select * from have
where EDMONTON_DATALINK=1 and GANDER_DATALINK=0 and GANDER_VOICE=0
	and FLT_BIZ_UID not in (select  FLT_BIZ_UID from have where not (EDMONTON_DATALINK=1 and GANDER_DATALINK=0 and GANDER_VOICE=0));
quit;

Re: Select Data with Same ID Based on Conditions

novinosrin — Wed, 24 Oct 2018 16:21:09 GMT

hi @BCNAV pretty straightforward with Boolean


data have;
input FLT_BIZ_UID  EDMONTON_DATALINK    GANDER_DATALINK     GANDER_VOICE ;
cards;
123456                                                   1                                           0                                       0                           
123456                                                   1                                           0                                       0
333333                                                   1                                           0                                       0
333333                                                   1                                           0                                       0
333333                                                   1                                           0                                       1
;

proc sql;
create table want as
select *
from have
group by FLT_BIZ_UID
having sum(EDMONTON_DATALINK=1 and GANDER_DATALINK=0 and GANDER_VOICE=0)=n(FLT_BIZ_UID);
quit;

Re: Select Data with Same ID Based on Conditions

novinosrin — Wed, 24 Oct 2018 16:28:36 GMT

The above can also be achieved with a Double DOW or an imposed interleave sorted by FLT_BIZ_UID too but lazy people like me who can't be bothered to type would prefer the above

Re: Select Data with Same ID Based on Conditions

mkeintz — Wed, 24 Oct 2018 16:43:01 GMT

Double DOW can appear busy, but here's a relatively brief equivalent:

data want (drop=_:);
  set have (in=in1) have (in=in2);
  by flt_biz_uid;
  if first.flt_biz_uid then call missing(_n1,_nqualify);
  _n1+in1;
  _nqualify+(in1=1 and EDMONTON_DATALINK=1 and GANDER_DATALINK=0 and GANDER_VOICE=0);
  if in2 and _n1=_nqualify;
run;

Unlike the proc sql solution, this requires data to be sorted by flt_biz_uid, but that tradeoff is that this would be faster for large datasets.

Re: Select Data with Same ID Based on Conditions

novinosrin — Wed, 24 Oct 2018 16:52:18 GMT

" tradeoff is that this would be faster for large datasets." -- Fully agree 🙂 .

EDIT to make a point representing my classmates who are (beginners to just intermediates):

Their concern:

"Hey Dude, Since you are regular in communities, can you let the community know our concern i.e

-- Sophisticated codes are lovely and we like it but makes us go back to community over and over the very moment we run into a hiccup"--Our 2 cents mate! DePaul CDM computing lab

I guess yours and DOW etc would fall into high sophistication. 🙂

Re: Select Data with Same ID Based on Conditions

DBailey — Wed, 24 Oct 2018 17:00:29 GMT

similar to below but I read your requirements a bit differently:

proc sql;
select * from have
where FLT_BIZ_UID not in 
(select  FLT_BIZ_UID from have where EDMONTON_DATALINK=0 or GANDER_DATALINK=1 or GANDER_VOICE=1);
quit;

Re: Select Data with Same ID Based on Conditions

mkeintz — Wed, 24 Oct 2018 20:46:50 GMT

I would suggest the classmates, or any subscriber, treat Sas communities like any crowd-sourced, free resource. It's a buffet - take only those offerings that are most suitable to your needs. However, over my own subscription tenure, I've often only wanted a "non-sophisticated" solution to a problem, but when I occasionally had time, I was happy to see other solutions that stretched my understanding.

And Yo DePaul CDM computing lab : There is no better status than student to justify putting forth a need for alternatives or more thorough explanations. As far as I can tell - all sas-related comments are welcome on this forum. So you don't need cover from @novinosrin - nobody will bite. Probably this note is the harshest you'd ever get.

Re: Select Data with Same ID Based on Conditions

s_lassen — Fri, 26 Oct 2018 09:23:05 GMT

I would do it like this:

data want;
  merge have(where=(not(Edmonton_datalink=1 and Gander_datalink=0 and Gander_voice=0)) in=wrong)
  have
  ;
  by FLT_BIZ_UID;
  if not wrong;
run;