BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DME790
Pyrite | Level 9

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

DateDayID
4-Apr-19ThursdayAAA
5-Apr-19FridayAAA
5-Mar-19TuesdayBBB
9-Apr-19TuesdayCCC

 

Merge table has

Test2

IDEMPLOYEE_NAME DIVISION_NMBRANCH_NM
AAAKate TelephonyNORTH
BBBJohn TelephonySOUTH
CCCMary TelephonyEAST

 

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.

 

DateDayIDEMPLOYEE_NAMEDivisionBranch
4-Apr-19ThursdayAAAKateTelephonyNORTH
5-Apr-19FridayAAA   
5-Mar-19TuesdayBBBJohnTelephonySOUTH
9-Apr-19TuesdayCCCMaryTelephonyEAST

 

Any help appreciated

 

Cheers

 

Dean

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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.

 

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;
FreelanceReinh
Jade | Level 19

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.

 

DME790
Pyrite | Level 9

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 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 503 views
  • 0 likes
  • 3 in conversation