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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.