BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cristinadumitru
Calcite | Level 5

Hi all,

I am fairly new to the advanced functionality of DMStudio and I am trying to set up a new data job that requires clustering of data and surviving record identification.

My input looks something like the below table.

e1a
e1a
e1a
e2b
e2b
e3c
e3c
e3c

I would like to have as output only the records that are repeated 3 times

e1a
e3c

Any ideas about what I could use to do so?

Thank you in advance,

Cristina

1 ACCEPTED SOLUTION

Accepted Solutions
skillman
SAS Employee

Cristina,

If you are querying a database directly, SQL can do this for you. DM Studio you can create an expression to help with this. Once you cluster the records, add an expression node which will return only clusters containing 3 records:

Job:

cluster_dm_job.png

Clustering:

dm_step_2.png

Desired Output:

dm_step_3.png

Expression configuration (Grouping):

dm_step_4.png

Expression configuration (Expression Logic):

dm_step_5.png

-shawn

View solution in original post

4 REPLIES 4
skillman
SAS Employee

Cristina,

If you are querying a database directly, SQL can do this for you. DM Studio you can create an expression to help with this. Once you cluster the records, add an expression node which will return only clusters containing 3 records:

Job:

cluster_dm_job.png

Clustering:

dm_step_2.png

Desired Output:

dm_step_3.png

Expression configuration (Grouping):

dm_step_4.png

Expression configuration (Expression Logic):

dm_step_5.png

-shawn

cristinadumitru
Calcite | Level 5

Hi Shawn,

Have you added any additional conditions?

My output is just:

scr.jpg

Thanks again,

Cristina

skillman
SAS Employee

When you are clustering, are you sorting by the cluster id? Does your cluster output resemble my second screenshot? Also, make sure in the expression, that you have indicated that you are grouping by the sorted cluster id.

cristinadumitru
Calcite | Level 5

My fault, I didn't see the grouping by cluster_id - never used the functionality before.

Thank you very much, it all works great now.

Best wishes,

Cristina

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1308 views
  • 4 likes
  • 2 in conversation