Hi All,
I'm trying to merge two files together.
Sorry I couldn't get this into 'test' code.
The first table has multiple entries for the ID and the second table has distinct ID's
Test
Date | Day | ID |
4-Apr-19 | Thursday | AAA |
5-Apr-19 | Friday | AAA |
5-Mar-19 | Tuesday | BBB |
9-Apr-19 | Tuesday | CCC |
Merge table has
Test2
ID | EMPLOYEE_NAME | DIVISION_NM | BRANCH_NM | |
AAA | Kate | Telephony | NORTH | |
BBB | John | Telephony | SOUTH | |
CCC | Mary | Telephony | EAST |
If I merge using the following code
data Final;
Merge Test
Test2 ;
By id;
run;
I get the result with one of the AAA's not showing any results for the merge.
Date | Day | ID | EMPLOYEE_NAME | Division | Branch |
4-Apr-19 | Thursday | AAA | Kate | Telephony | NORTH |
5-Apr-19 | Friday | AAA | |||
5-Mar-19 | Tuesday | BBB | John | Telephony | SOUTH |
9-Apr-19 | Tuesday | CCC | Mary | Telephony | EAST |
Any help appreciated
Cheers
Dean
Hi @DME790,
You would get this result if variables EMPLOYEE_NAME, DIVISION_NM and BRANCH_NM were also contained in dataset TEST (with missing values, at least for the second observation). With the system option
options msglevel=I;
helpful messages in the log will notify you about situations like this in a MERGE statement, e.g.:
INFO: The variable EMPLOYEE_NAME on data set WORK.TEST will be overwritten by data set WORK.TEST2.
Works fine for me?
data Test;
input Date:date9. Day $ ID $;
format date date9.;
datalines;
4-Apr-19 Thursday AAA
5-Apr-19 Friday AAA
5-Mar-19 Tuesday BBB
9-Apr-19 Tuesday CCC
;
data Test2;
input ID $ EMPLOYEE_NAME $ DIVISION_NM :$20. BRANCH_NM $;
datalines;
AAA Kate Telephony NORTH
BBB John Telephony SOUTH
CCC Mary Telephony EAST
;
data Final;
merge Test Test2;
by ID;
run;
proc print data=Final;
run;
Hi @DME790,
You would get this result if variables EMPLOYEE_NAME, DIVISION_NM and BRANCH_NM were also contained in dataset TEST (with missing values, at least for the second observation). With the system option
options msglevel=I;
helpful messages in the log will notify you about situations like this in a MERGE statement, e.g.:
INFO: The variable EMPLOYEE_NAME on data set WORK.TEST will be overwritten by data set WORK.TEST2.
Thanks @FreelanceReinh,
Makes perfect sense - just removed all the duplicate values in the data set that had blanks and works the way it should.
Cheers
Dean
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: