SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Clustering in Data Management Studio

Reply
Contributor
Posts: 20

Clustering in Data Management Studio

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

Attachment
SAS Super FREQ
Posts: 90

Re: Clustering in Data Management Studio

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

Contributor
Posts: 20

Re: Clustering in Data Management Studio

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

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