I have ID1 and ID2 as two variables in different datasets.
Small example:
ID1
A01
A02
A03
ID2
101
102
103
How would I merge based on the right-most two characters?
I have explored possibilities of PROC SQL but was unable to find a simple solution.
on a substr = b substr would result in cartesian. What results do you want in your output?
easy way:
data one;
input ID1 $;
k=substr(id1,2);
cards;
A01
A02
A03
;
data two;
input ID2 $;
k=substr(id2,2);
cards;
101
102
103
;
data want;
merge one two;
by k;
run;
Hello, thanks for your response!
This is part of a bigger project. Essentially, I am combining two excel worksheets. I am merging them based off of one column which I simplified to ID1 and ID2 in this example. Problem is, ID2 only contains some of the string of ID1. However I need to do data matching based on this variable.
Please post a best representative and comprehensive sample that you can, so that it's easy for everybody. Then explain your requirement. I am sure many would love to offer you the solution you need
Also, if you are merging excel sheets, You are better off doing that in excel vba rather than SAS in my opinion.
This does one form of match. Please notice the way to provide example data that code can be tested with. Also I added different length variables to show 1) that the last 2 characters are compared ignoring length and 2) to show an example of what may not be desirable for the match, 37 vs 237, just in case. The JOIN type depends on which records you may want in case of not match from the first set or the second set.
data work.d1; input ID1 $; datalines; A01 A02 A03 AB37 ; run; data work.d2; input ID2 $; datalines; 101 102 103 PDQ237 ; run; proc sql; create table work.both as select a.id1, b.id2 from work.d1 as a left join work.d2 as b on substr(a.id1, length(a.id1)-1)=substr(b.id2,length(b.id2)-1) ; quit;
SQL works fine. Assuming both are character
from table1
left join table2
on substr(id1, 1, 2) = substr(id2, 1, 2)
.....
If they're numeric you'll need a slightly different approach but this should give you the idea.
@Ramsha wrote:
I have ID1 and ID2 as two variables in different datasets.
Small example:
ID1
A01
A02
A03
ID2
101
102
103
How would I merge based on the right-most two characters?
I have explored possibilities of PROC SQL but was unable to find a simple solution.
Hello all,
Please see attached thread that I just posted for more information on the question. Thank you so much!
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: