How to merge excel data together based on variable that contains some matching elements?

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

How to merge excel data together based on variable that contains some matching elements?

[ Edited ]

How would I be able to merge these two data tables together based on the variable ID1 and ID2. As you can see, the 3 right most numbers in the Variables ID1 and ID2 will match. However I do not know how to find a solution that would merge the data sets to each other based off of ID1 and ID2. The solution should have two duplicates of City, State/ZIP, State, and ZIP. Basically I need to put these two data sets together based on the Variable ID1, ID2. 

 


 

 


Accepted Solutions
Solution
a month ago
Super User
Posts: 23,988

Re: How to merge excel data together based on variable that contains some matching elements?

The SQL approach I showed in your other thread is still correct, just change the values in the SUBSTR to get only the last 3. Probably to substr(var, 4, 3)

 


@Ramsha wrote:

The New Haven observation on Table 1  should match with Portland observation Table 2. The Bainbridge observation on Table 1 should match with Fairfield observation on Table 2. Basically you are matching based on last 3 digits on the ID1/ID2 column. 


 

View solution in original post


All Replies
Super User
Posts: 10,555

Re: How to merge excel data together based on variable that contains some matching elements?

What is the rule for matching, and which ID1 would therefore be matched with which ID2?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 32

Re: How to merge excel data together based on variable that contains some matching elements?

Posted in reply to KurtBremser

The New Haven observation on Table 1  should match with Portland observation Table 2. The Bainbridge observation on Table 1 should match with Fairfield observation on Table 2. Basically you are matching based on last 3 digits on the ID1/ID2 column. 

Solution
a month ago
Super User
Posts: 23,988

Re: How to merge excel data together based on variable that contains some matching elements?

The SQL approach I showed in your other thread is still correct, just change the values in the SUBSTR to get only the last 3. Probably to substr(var, 4, 3)

 


@Ramsha wrote:

The New Haven observation on Table 1  should match with Portland observation Table 2. The Bainbridge observation on Table 1 should match with Fairfield observation on Table 2. Basically you are matching based on last 3 digits on the ID1/ID2 column. 


 

Super User
Posts: 10,555

Re: How to merge excel data together based on variable that contains some matching elements?

Use

scan(id1,2,'-')
scan(id2,2,'-')

to extract the relevant portion.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 79 views
  • 3 likes
  • 3 in conversation