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

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

Ramsha — Wed, 18 Jul 2018 18:35:07 GMT

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.

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

Kurt_Bremser — Tue, 17 Jul 2018 16:50:38 GMT

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

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

Ramsha — Tue, 17 Jul 2018 16:53:29 GMT

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.

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

Reeza — Tue, 17 Jul 2018 16:54:56 GMT

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.

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

Kurt_Bremser — Tue, 17 Jul 2018 16:59:54 GMT

Use

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

to extract the relevant portion.