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

Extracting distinct values using an Extract in DI Studio

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Extracting distinct values using an Extract in DI Studio

I have an input table that includes a variable called "insured_sk". It contains a few duplicate values. How do I use a Extract transformation to ensure that only distinct insured_sk values are extracted? I figure there should be an expression I can use.

I realize that this is the kind of question that I'll be hitting myself in the head about once i hear the answer, as it's probably very, very simple.

Thanks for your attention. Smiley Happy


Accepted Solutions
Solution
‎10-17-2012 09:48 AM
Super User
Posts: 5,257

Re: Extracting distinct values using an Extract in DI Studio

Here's a punch:

there is a Distinct option in the extract transform.

Since this is SQL, it will keep distinct records, not distinct columns, so be sure to drop any columns that will prevent you from having distinct values.

Data never sleeps

View solution in original post


All Replies
Solution
‎10-17-2012 09:48 AM
Super User
Posts: 5,257

Re: Extracting distinct values using an Extract in DI Studio

Here's a punch:

there is a Distinct option in the extract transform.

Since this is SQL, it will keep distinct records, not distinct columns, so be sure to drop any columns that will prevent you from having distinct values.

Data never sleeps
N/A
Posts: 1

Re: Extracting distinct values using an Extract in DI Studio

I am trying to do the same thing. The answer provided does not really address the issue. I want ALL the columns in a row; but wish to purely do a distinct(col1). Of course you could do a join on the table containing only the distinct column with the original table but even then you would get multiple rows with multiple records from this table. It is weird that an option does not exist to select a column(s) for the distinct keyword other than writing code.

Super User
Posts: 5,257

Re: Extracting distinct values using an Extract in DI Studio

Again, this due to the limitations of SQL.

Sounds like you want to do a NODUPKEY. For that, use the Sort transformation.

Data never sleeps
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1579 views
  • 0 likes
  • 3 in conversation