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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.