topic Re: How to merge if variable contains some matching observations to another variable? in SAS Programming

How to merge if variable contains some matching observations to another variable?

Ramsha — Tue, 17 Jul 2018 16:18:28 GMT

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.

Re: How to merge if variable contains some matching observations to another variable?

novinosrin — Tue, 17 Jul 2018 16:26:47 GMT

on a substr = b substr would result in cartesian. What results do you want in your output?

Re: How to merge if variable contains some matching observations to another variable?

novinosrin — Tue, 17 Jul 2018 16:31:10 GMT

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;

Re: How to merge if variable contains some matching observations to another variable?

Ramsha — Tue, 17 Jul 2018 16:32:30 GMT

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.

Re: How to merge if variable contains some matching observations to another variable?

novinosrin — Tue, 17 Jul 2018 16:36:41 GMT

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.

Re: How to merge if variable contains some matching observations to another variable?

ballardw — Tue, 17 Jul 2018 16:37:28 GMT

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;

Re: How to merge if variable contains some matching observations to another variable?

Reeza — Tue, 17 Jul 2018 16:42:24 GMT

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.

Re: How to merge if variable contains some matching observations to another variable?

Ramsha — Tue, 17 Jul 2018 16:49:19 GMT

Hello all,

Please see attached thread that I just posted for more information on the question. Thank you so much!

https://communities.sas.com/t5/General-SAS-Programming/How-to-merge-excel-data-together-based-on-variable-that-contains/m-p/478716#M58613