Proc SQL MERGE

[Ankur32]

I have 2 data sets, One is containing terminated employees of the organisation and other contains full employee list(working and terminated both) now I want a data set which contain only working employee. I want to use proc sql merge. Please suggest which merge will get the correct data set.

Thanks in advance !!

[MarkWik]

Where are the samples of the datasets? 

[Ankur32]

I don't have data sets, question was asked in an interview.

[Cynthia_sas]

Hi:
You can do either an SQL JOIN or a DATA step MERGE to get what you want. Does the full employee list have any variable such as termination date that you might use for filtering, instead of doing a JOIN or MERGE? Just curious how the "full" employee data might distinguish between employees who need to be paid and employees who do not need to be paid.

Cynthia

[Ankur32]

Hi,

thanks for replying. I have only employee id's. which join will give correct answer?

[novinosrin]

all you need is a simple look up with a subquery like:

proc sql;
create table want as
select *
from  full_employee 
where employee_id not in (select employee_id from terminated);
quit;

[Ankur32]

Hi,

Thanks very much for the reply. Shall we use Join? This question was asked in an interview and they asked me which join will be used to get the data set.

[novinosrin]

You mentioned "I want a data set which contain only working employee."

Challenge your interviewer why you need a join. The need for join arises only when you need to extract a variable/variables from the look up dataset. Your interview question doesn't seem to mandate a pull of any external information. All the best!

[JeffMeyers]

This is definitely what I would do in this situation. No need to actually merge datasets.

[PGStats]

If all you had were IDs you could do:

proc sql;
create table want as
select employee_id from full_employee 
except
select employee_id from terminated;
quit;

again, not a join.

[kiranv_]

proc sql;
create table want as
select a.employee_id
from  full_employee  a
full join 
terminated b
on a.employee_id = b.employee_id
where b.employee_id is missing

with join need to do a full join followed by employee_id is missing for terminated table