DATA Step, Macro, Functions and more

Proc SQL MERGE

Reply
Contributor
Posts: 20

Proc SQL MERGE

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

Frequent Contributor
Posts: 98

Re: Proc SQL MERGE

Where are the samples of the datasets? 

Contributor
Posts: 20

Re: Proc SQL MERGE

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

SAS Super FREQ
Posts: 9,256

Re: Proc SQL MERGE

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
Contributor
Posts: 20

Re: Proc SQL MERGE

Posted in reply to Cynthia_sas

Hi,

 

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

 

PROC Star
Posts: 1,300

Re: Proc SQL MERGE

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;
Contributor
Posts: 20

Re: Proc SQL MERGE

Posted in reply to novinosrin

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.

PROC Star
Posts: 1,300

Re: Proc SQL MERGE

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!

 

 

Occasional Contributor
Posts: 7

Re: Proc SQL MERGE

Posted in reply to novinosrin
This is definitely what I would do in this situation. No need to actually merge datasets.
Esteemed Advisor
Posts: 5,392

Re: Proc SQL MERGE

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.

 

PG
PROC Star
Posts: 499

Re: Proc SQL MERGE

 

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

 

Ask a Question
Discussion stats
  • 10 replies
  • 223 views
  • 4 likes
  • 7 in conversation