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 !!
Where are the samples of the datasets?
I don't have data sets, question was asked in an interview.
Hi,
thanks for replying. I have only employee id's. which join will give correct answer?
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;
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.
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!
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.