topic Re: how to merge datasets with variables containing not exactly similar values in SAS Programming

how to merge datasets with variables containing not exactly similar values

knveraraju91 — Thu, 08 Sep 2016 20:55:41 GMT

Dear,

I have to merge two datasets based on one variable(test). The values in the variables mean same thing but the values are not exactly similar. EG; EXCL01=EX1.

Please help. Thanks

data1;

test VALUE

EXCL01 A

EXCL07A B

EXCL11 C

EXCL07 D

INCL01 E

INCL22 H

INCL03A J

INCL03B H

data2

ID TEST

1 EC1

2 EC11

3 EC7

4 IN01

5 IN03a

6 IN03b

7 IN22

OUTPUT NEED;

ID test VALUE

1 EXCL01 A

2 EXCL11 C

3 EXCL07 D

4 INCL01 E

5 INCL03A J

6 INCL03B H

7 INCL22 H

Re: how to merge datasets with variables containing not exactly similar values

Reeza — Thu, 08 Sep 2016 21:03:21 GMT

The term is fuzzy matching. But you have to have some rules. Programs basically implement rules 🙂

So how do you want to match? What's a close enough match? Does it matter how long the strings are?

Look at compged/spedis/sounds like operators for matching and distances between strings.

If you search fuzzy matching at lexjansen.com you'll find a lot of different approaches.

Re: how to merge datasets with variables containing not exactly similar values

Astounding — Thu, 08 Sep 2016 21:11:05 GMT

It looks relatively straightforward to add a third variable to DATA1, an abbreviated version of TEST that would match the values found in TEST within DATA2. Then sort and merge by that new variable.