Clustering in Data Management Studio

[Helle]

Hi,

I am trying to compare lists of values which originate from different tables. The format is typically like this:

ID              Description             Table_Name

ABC          Testing                   Table_A

ABC          Testing                   Table_B

ABC          TEsting                   Table_C

In most cases, the descriptions are exactly the same but sometimes there is a small difference (like in Table_C above). I would like to extract a list of those rows that have the same ID but different descriptions. I assume that I can use the clustering functionality in Data Management Studio but I am not quite sure how. Does anyone have a suggestion? I have attached a small data job with some sample data (DM Studio 2.6). I only have access to DM Studio so I cannot make use of SAS code.

Thanks, Helle

[RonAgresta]

Hi Helle,

If you can use the ID to identify the rows/values you want to compare, you can:

• Sort by the ID
• Use the expression node to group by that ID
• Start with the first description value you encounter for that group
• Then compare that value to subsequent description values in the group using the edit distance function
• Use the pushrow function to create a new row to flag that group for review. Set the option to flag the row as "pushed"
• Reset the initial value in between groups to start the comparison anew for each group

If you then use a branch node with some filter nodes on each branch, you can capture the IDs for groups with some issues to review (filter to only keep pushed rows), and join back to the other side of the branch to limit the results to just groups with differences.

If the ID can't be used to create groups, use a match code on the description value and then use a cluster node to group possible similar records, sorting after the cluster operation. Then follow the steps above but use the cluster ID instead of the ID value for groups.

Ron

[Helle]

Hi Ron,

Thanks for your reply. Unfortunately, I lack the EEL skills to be able to follow your instructions. It would be great if you could provide me with an example of how to code the steps starting with "Start with the first description value.." and ending with "Reset the initial value...".

Thanks a lot,

Helle