DATA Step, Macro, Functions and more

how to merge datasets with variables containing not exactly similar values

Reply
Super Contributor
Posts: 272

how to merge datasets with variables containing not exactly similar values

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

                         

Super User
Posts: 19,867

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

Posted in reply to knveraraju91

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

 

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.

Super User
Posts: 5,516

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

Posted in reply to knveraraju91

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.

Ask a Question
Discussion stats
  • 2 replies
  • 221 views
  • 0 likes
  • 3 in conversation