Steven, Data Management Studio has a few nodes in Data Jobs, when used in conjunction, can help you solve your problem. The first node to be aware of is the Matchcode node which is under the Entity Resolution section. This node generates fuzzy representations of input data which can be used for matching purposes. In your case, you have provided a single field of organization names, so you would generate a matchcode using the “Organization” definition (if your locale and definition list is blank, then you will need to download a Quality Knowledge Base from the SAS website to enable this functionality). Since you have only provided a single field, you will need to push that sensitivity up to 95 to prevent over matching as much as possible. One word of warning, you will get lots of overmatching when matching on a single field. Matchcode definitions have been designed to use with combinations of fields such as Name and Address or Name and Phone. If you have other attributes available for you to match on, go ahead and include those as well. You will need to generate matchcodes for each of your data sources that you are joining together. In your example, this would be both columns of your dataset. Now that you have a fuzzy representation of the data, you need to bring the data together for matching purposes. This now comes down you your personal preference on how you want the data brought together. The first approach is to Union (under Data Integration) the sources together. This will bring the matchcode and the company names together in a single column. You can then use a Clustering (under Entity Resolution) step on matchcode to group like items together and assign them a common cluster id and sorting the records by that cluster id. Finally, you can use a Surviving Record Identification (under Entity Resolution). This step allows you to choose a best record per the rules you have set. Another option is to Join (under Data Integration) your two data sources together where the matchcodes from both sources match each other. Then carry forward the Company name from data source A and the Org name from data source B. By default, this is an inner join so this node will only output matching fields. In your case, you wanted records that didn’t match as well, so you would need to perform a full outer join. For non-matching records, the field name will be null for the other table. Then you can pass these through an expression statement that will create a new string column for your final company name and issues an if statement to pull the right data: string company if isnull(comp_name) company = org_name if isnull(org_name) company = comp_name else company = comp_name This approach will result in overmatching as well as records being repeated records. For instance FX Alliance Inc in your sample is a Fuzzy match to FX Solutions and FX Alliance. This is an overmatch because Solutions and Alliance are both noise words in the Organization definition, thus are stripped and look the same (see warning above). Regards, Mike
... View more