topic Extracting distinct values using an Extract in DI Studio in SAS Data Management

Extracting distinct values using an Extract in DI Studio

EinarRoed — Wed, 17 Oct 2012 09:37:56 GMT

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.

Re: Extracting distinct values using an Extract in DI Studio

LinusH — Wed, 17 Oct 2012 13:48:23 GMT

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.

Re: Extracting distinct values using an Extract in DI Studio

sprogger — Wed, 12 Jun 2013 02:00:23 GMT

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.

Re: Extracting distinct values using an Extract in DI Studio

LinusH — Wed, 12 Jun 2013 07:00:29 GMT

Again, this due to the limitations of SQL.

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