topic Re: help with Merge in SAS Enterprise Guide

help with Merge

DME790 — Tue, 14 May 2019 07:09:26 GMT

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

Re: help with Merge

PeterClemmensen — Tue, 14 May 2019 07:21:25 GMT

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;

Re: help with Merge

FreelanceReinh — Tue, 14 May 2019 08:46:07 GMT

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.

Re: help with Merge

DME790 — Tue, 14 May 2019 23:31:37 GMT

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