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

[tmes]

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.

[Patrick · Accepted solution]

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.

[ed_sas_member]

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,