topic Re: Clustering in Data Management Studio in SAS Data Management

Clustering in Data Management Studio

Helle — Fri, 01 Apr 2016 08:14:37 GMT

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

Re: Clustering in Data Management Studio

RonAgresta — Sat, 02 Apr 2016 12:31:43 GMT

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

Re: Clustering in Data Management Studio

Helle — Wed, 27 Apr 2016 13:22:50 GMT

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