topic Re: Selecting records from one data set that aren't in another in SAS Programming

Selecting records from one data set that aren't in another

tmes — Wed, 26 Feb 2020 01:22:14 GMT

Hi,

So I'm working in EG 7.1 and I have a dataset with over 4 million records and another dataset with around 200,000 records. For explanation purposes I'll call them lunch and time, respectively.

I need to output all the person_ids from lunch that aren't in time. I have tried the following -

proc sql;
create table lunchtime as
select *
from lunch t1
where person_id not in (select person_id from time);
quit;
Proc SQL except function
Left Join
If in a and not b function

Functionally, the code works. It's outputting around 3 million records which makes sense, however the result I need to get to is approx. 150,000. At this point I'm thinking that there's something my boss has forgotten to tell me about the data, however I was just wondering if anyone has any other ideas on how to do this?

Thanks in advance.

Re: Selecting records from one data set that aren't in another

Patrick — Wed, 26 Feb 2020 01:48:06 GMT

If I understand this right then you've used multiple coding approaches for combining the data and you've always got the same result. Looks very much like your boss didn't tell you something - or the data is different from what your boss thinks it is.

May be run a proc freq over your lunch dataset to see if there are some DQ issues like a few matching id's with very high (too high) volumes.

Re: Selecting records from one data set that aren't in another

ed_sas_member — Wed, 26 Feb 2020 09:05:29 GMT

Hi @tmes

In addition to @Patrick 's comment, I wonder if lunch or times datasets have only one or many records per person_id?

In case there are many, maybe you need to suppress duplicate records to retrieve unique IDs.

Best,