I have three separate data sets below. I want to use a data step to show for each of the new employees and interns who their manager is and which department they will work in. Also, which department has the most new people. I don't know how to sort the new_employee and new_intern data sets to then have them merge with the manager data set.
data New_Employee; length name $15; input name $ team training_course work_experience; datalines; Bohr,Neils 1 0 3 Zook,Carla 2 2 2 Penrose,Roger 1 3 1 Martinez,Maria 3 1 2 Orfali,Philip 2 1 1 ; run; data New_Intern; length name $15 major $20; input name $ team training_course major $; datalines; Capalleti,Jimmy 3 1 FashionDesign Chen,Len 2 0 BusinessAnalytics Cannon,Annie 1 0 Mathematics Davis,Brad 3 0 Art Einstein,Albert 1 1 ComputerScience ; run; data Manager; length mname $15 department $10; input mname $ team department $; datalines; Wilson,Kenneth 1 Operation Bardeen,John 2 Marketing Sorrell,Joseph 3 Design ; run;
The only variable you can possibly merge by is TEAM, as that exists in all three data sets.
Therefore, you would have to sort all three data sets by TEAM before doing the merge.
Something like this?
proc sort data=New_Employee(keep=name team) out=emp;
by team name;
run;
proc sort data=New_Intern(keep=name team) out=int;
by team name;
run;
data all;
set emp int;
by team name;
run;
proc sort data=Manager;
by team;
run;
data want;
if 0 then set all manager; /* just to get the right order of variables */
do count=1 by 1 until(last.team);
set all;
by team;
end;
do until(last.team);
merge all manager;
by team;
output;
end;
run;
Below using SQL.
proc sql;
select
m.*
,emp.emp_type
,emp.name
,emp.training_course
,emp.work_experience
,emp.major
from manager m
left join
(
select
team
,'new_employee' as emp_type
,name
,training_course
,work_experience
,'' as major
from new_employee
union corr all
select
team
,'new_intern' as emp_type
,name
,training_course
,major
,. as work_experience
from new_intern
) emp
on m.team=emp.team
order by team, name
;
quit;
How does this relate to the original question about combining 3 data sets???
This looks like it should be a completely new topic as your requirements are very different from the original question starting this thread.
Also, you seem not to actually want an Append (add records) as an UPDATE, which would replace the values in the master table from a new (transaction) data set based on matching criteria, likely the ID. An update will add new records if the Id is new.
If you have corrupted your master data set by creating records with duplicate ids then you need to recover the old version before you start.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: