topic Re: Proc SQL MERGE in SAS Programming

Proc SQL MERGE

Ankur32 — Sat, 28 Apr 2018 14:23:29 GMT

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 !!

Re: Proc SQL MERGE

MarkWik — Sat, 28 Apr 2018 14:45:26 GMT

Where are the samples of the datasets?

Re: Proc SQL MERGE

Cynthia_sas — Sat, 28 Apr 2018 14:47:19 GMT

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

Re: Proc SQL MERGE

Ankur32 — Sat, 28 Apr 2018 14:49:43 GMT

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

Re: Proc SQL MERGE

Ankur32 — Sat, 28 Apr 2018 14:52:33 GMT

Hi,

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

Re: Proc SQL MERGE

novinosrin — Sat, 28 Apr 2018 14:59:37 GMT

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;

Re: Proc SQL MERGE

Ankur32 — Sat, 28 Apr 2018 15:06:36 GMT

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.

Re: Proc SQL MERGE

novinosrin — Sat, 28 Apr 2018 15:27:49 GMT

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!

Re: Proc SQL MERGE

JeffMeyers — Sat, 28 Apr 2018 15:30:24 GMT

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

Re: Proc SQL MERGE

PGStats — Sat, 28 Apr 2018 20:58:01 GMT

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.

Re: Proc SQL MERGE

kiranv_ — Sun, 29 Apr 2018 15:03:19 GMT

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