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.
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.
What is the rule for matching, and which ID1 would therefore be matched with which ID2?
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.
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.
Use
scan(id1,2,'-')
scan(id2,2,'-')
to extract the relevant portion.
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!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.