I have two datasets that contain a date, a customer name and a narrative description of merchandise. For each line in data1 I am looking for a matching line in data2. Manually, it works, but it is time-consuming so that approach is for small datasets. Now I have thousands of lines. The primary obstacle is that the narrative descriptions are long, contain irrelevant words, and have inconsistent formats and errors. The key words within the description are a model # (which may or may not exist in either data1 or data 1), an overt indicator for the number of pieces (i.e., 51 PCE), and most importantly, dimensions. However, the dimensions are inconsistent: one dataset may use x between dimensions (1500mm x 350mm x 20mm) while another uses *. There are other issues such as no or inconsistent spaces in the dimensions. Nothing is in a set position, sometimes there is not a model, the unimportant words vary, and occasionally the dimensions switch between mm and cm. Plain and simple, the data is dirty.
My idea is to build a SAS program that, goes line by line in data1, trying each and every term within the description string and try to match against each and every term in every line of data2 and keep a count of the matches. I then rank lets say the Top 10 possible matches for each line. While this is a new knowledge area for me in terms of SAS, I bet I am not the first to encounter this obstacle. I would appreciate any insight.
Here is some sample data. For simplicity, I have deleted the date and customer name field:
Data1:
2QG850-19 natural topaz stone, size 1099 x 648 mm, 2cm thick (62 PCE), 100% brand new
Data2
EGNL01 natural topaz stone, thickness 2cm, size 2990x1280mm (25 PCE), 100% brand new
Natural topaz stone, size 578 mm x 648 mm, thickness 30 mm, 100% new
natural stone, size 991 mm x 610 mm, thickness 30 mm, 100% new
topaz stone, size 1905 mm x 921 mm, thickness 30 mm, 100% new
... View more
