I need to match merge 3 datasets, one of which was created via concatenation. I am ultimately trying to make a dataset that contains a list of people that passed the base and advanced certification test. Based on the code that is below, the output shows all employees and their salaries but no date. I'm sure it has something to do with the fact that 2 of the 3 datasets have the "date" variable, but I'm unsure how to address this.
Ultimately, I need to match merge three datasets and create a new dataset that lists the name, 2 certification dates (date of Base and Advanced certification), and salary of only the people that passed both certifications. My code is as follows, with the two additional datasets attached:
/***Problem #1***/
data Dept1Names;
input name $ empid $;
datalines;
NELSON 254
MITCHELL 362
PEREZ 910
CARTER 935
;
data Dept1Salaries;
input salary id $;
datalines;
103100 254
100200 362
73500 910
88400 935
;
data Dept1;
merge Dept1Names Dept1Salaries;
keep name salary;
run;
/***Problem #2***/
data Dept2;
input name $ salary;
datalines;
ROBERTS 74000
TURNER 89200
PHILLIPS 87000
CAMPBELL 104600
PARKER 87800
EVANS 73100
EDWARDS 72300
COLLINS 100500
;
data Dept3;
input name $ salary;
datalines;
STEWART 102600
SANCHEZ 103300
MORRIS 87200
ROGERS 70700
REED 73000
COOK 88900
;
data Employee;
set Dept1 Dept2 Dept3;
proc sort data = Employee;
by name;
run;
/***Problem #3***/
data Advanced;
input name $ date;
datalines;
CAMPBELL 12/01/2012
COOK 03/01/2011
EDWARDS 02/01/2013
MORRIS 02/04/2013
PEREZ 06/01/2011
ROGERS 07/01/2012
SANCHEZ 09/01/2012
STEWART 1/19/2013
TURNER 05/01/2011
;
data Base;
input name $ date;
datalines;
CAMPBELL 01/01/2008
CARTER 11/01/2009
COLLINS 02/01/2005
COOK 06/01/2010
EDWARDS 06/01/2010
EVANS 05/01/2009
MITCHELL 12/01/2010
MORRIS 11/01/2012
NELSON 08/01/2010
PEREZ 02/01/2008
PHILLIPS 01/01/2011
ROGERS 10/01/2008
SANCHEZ 11/01/2009
STEWART 03/01/2012
TURNER 02/01/2006
;
data Both;
merge Employee Advanced Base;
by name;
keep name date salary;
run;
