topic Select Distinct from Group in SAS Data Management

Select Distinct from Group

jwhite — Thu, 05 Jan 2017 20:02:06 GMT

If my dataset has 10 columns, but I only want distinct rows selected based on the values of four of those...can it be done? I want all 10 columns in my out put, but again, select distinct only being evaulated on four of those columns.

Can this be done in an extract node in DI Studio as well?

Thanks!

Re: Select Distinct from Group

Reeza — Thu, 05 Jan 2017 20:13:33 GMT

Yes, but you do need to specify some rules so you're sure on how the other values are selected. Assuming they aren't identical, how do you want them to be selected?

ie the first four (order doesn't matter) columns are identical, but the next one isn't. Which row will be selected? What are the rules? That will define your logic.

A B C D 10

A B C D 30

A B C D 50

Re: Select Distinct from Group

jwhite — Thu, 05 Jan 2017 21:30:57 GMT

Let's say i want the highest value in the fifth column...where do we go from there?

I'm attampting to do this in DI Studio, but would like the proper syntax as well for understanding.

Thanks!

Re: Select Distinct from Group

LaurieF — Thu, 05 Jan 2017 22:20:02 GMT

Is this what you want? I'm often wary of using distinct when using group by, because it can distort the results.

data source;
infile cards;
attrib col1-col4 length=$ 1;
attrib col5 length=3;
input col1-col5;
cards;
A B C D 10
A B C D 10
A B C D 30
A B C D 50
;
run;

proc sql;
create table result as
   select col1,
          col2,
          col3,
          col4,
          max(col5) as max_col5
     from source
    group by col1,
             col2,
             col3,
             col4;
quit;

Re: Select Distinct from Group

Reeza — Thu, 05 Jan 2017 22:22:34 GMT

I'm not sure if you're using custom SQL or the GUI tools in DI Studio. I suspect the latter, so I'll leave that for someone else to answer since I don't work with DI Studio. For custom SQL, @LaurieF answer is correct, you would use an aggregate function in the SQL to select the fifth and other columns.

Re: Select Distinct from Group

LaurieF — Thu, 05 Jan 2017 22:25:57 GMT

Both the extract and SQL queries in DI will allow you to generate code virtually identical to my hand-cut suggestion; note that extract doesn't allow the having clause.

I don't have DI here to show what it would look like, but I can talk you through it if you like. It's best for debugging purposes not to use custom SQL code - let the transformation tool do all the heavy lifting if you can get away with it.

Re: Select Distinct from Group

jwhite — Thu, 05 Jan 2017 22:34:16 GMT

@LaurieF I know there is the option to include some custom code in DI Studio transformations. I feel pretty proficient with the basic use of the nodes, so if you've got tips on how to do this via DI Studio Extract node (or other), I'd love to see them.

Thanks!

Re: Select Distinct from Group

LaurieF — Thu, 05 Jan 2017 22:42:09 GMT

I'm going to rudely assume that you've not used too much DI and start from scratch (sorry!). I shall also make mistakes because I'm doing it from memory… (Sorry again)

Plonk your source table in the project and drag over the SQL Extract transformation and join them one to another.

Open up the transformation and map the first four columns to the target. If they're there already (I hate auto-mapping with a passion), remove the fifth column, and any of the other columns that may have been put there autoblackmagically.

For the fifth column, make its name in the target area something meaningful. In the expression field, enter

max(col5)

In the group by tab (again I'm doing this from memory), select the first four columns.

The result will only be in a work dataset, of course, but I'll leave all the rest to you.

This should work.

Re: Select Distinct from Group

Patrick — Fri, 06 Jan 2017 02:38:37 GMT

@jwhite

If I don't know how to do something in DIS then I normally start with working out how the code logic should look like. That's the SQL @LaurieF already provided.

The functionality of the SQL Extract transformation is a sub-set of the SQL Join transformation. It happened to me more than once that I've used the Extract transformation and then came a change where the required functionality was only available in the SQL Join transformation and I had to re-build everything. For this reason I've stopped using the Extract transformation at all and always use the Join transformation to begin with.

What you want to do is pretty basic. Here some screenshots.

Sample job:

SQL Join Node "Aggregate by name and sex":

Re: Select Distinct from Group

LaurieF — Fri, 06 Jan 2017 08:08:41 GMT

Ah - that's the one. Thank you, @Patrick. I should have checked for that sort of example. And if only I could convince SAS Inst to give me a copy of SAS/DI…

I concur over the Extract/Join comparison. I use Extract when I know that the transformation will remain a simple one, but I much prefer the power of the join.

Re: Select Distinct from Group

Kritsana — Fri, 06 Jan 2017 09:37:53 GMT

Let try "Sort" node to order 5 keys(#5 key is the descending value) which you mention, then use "Data Validation" node to reject duplicate row by 4 keys (select option: Move all but one row to error table).

hope it can help you. 🙂

Re: Select Distinct from Group

Patrick — Fri, 06 Jan 2017 08:53:22 GMT

@Kritsana

What you propose could be done with the Sort node only using NODUPKEY. The disadvantage of this method is that the selected non-key variable values will be "random".

Re: Select Distinct from Group

Kritsana — Fri, 06 Jan 2017 09:35:42 GMT

@Patrick
Thank you very much for your advised. My propose is just provided another way to get result, the reason to use Data Validation node because jwhite want to descending column #5 if there're more duplication values in 4 keys.

Re: Select Distinct from Group

jwhite — Fri, 06 Jan 2017 15:49:22 GMT

Wow! I used the Join statement, messaged it a bit more to suit my needs, and it worked.

Can you guys expand a little more on using Join instead of Extract (when not actually joining multiple tables)? I hadn't heard of or considered this before.

Thanks!

Re: Select Distinct from Group

jwhite — Fri, 06 Jan 2017 21:35:52 GMT

I had some extra time this afternoon and attempted to do the same thing with an Extract node...and I was.

@Patrick, @LaurieF, can you please just elaborate a little more why I should consider using joins more fequently!

Thanks and have a great weekend!

Re: Select Distinct from Group

LaurieF — Fri, 06 Jan 2017 22:24:04 GMT

The main difference when you've only got one source dataset is that the Extract transformation doesn't support having. But the extra flexibility (ease of changing procedure and dataset options, for example) make it more superior again. I still use Extract, but only when I know that the requirements for using it are simple and will (in all likelihood) not change.

Personally, when I'm writing SAS by hand I tend towards using a data step, because of the control and often sheer speed it gives me.

When joining two or more tables together, I chop and change between an SQL join and a dataset merge/update. It depends on the structure (especially width) and overall size of the datasets. I do have a generic DI transformation that I've written that does a dataset merge - it requires that the two source datasets are sorted. Even with massively denormalised tables on both sides, it screams through - something that SQL has trouble doing.

Re: Select Distinct from Group

jwhite — Mon, 09 Jan 2017 15:44:16 GMT

@LaurieF, how would this logic work of the rows have different values in the grouped columns?

For example:

A B C D 10
A B C D 10
A B C D 30
A B C D 50 
A B H D 10
A B H D 10
A B H D 10
A B H D 20
F F F D 10
F F F D 30

With the above and doing a max() on the fifth column...the value in the 5th column for all rows will be 50, which is the highest. Seemingly even when I group the first four. I get:

A B C D 50

A B H D 50

F F F D 50

What I desire would be:

A B C D 50

A B H D 20

F F F D 30

I guess I would think that the max() would evaluate for the highest value within the group.

Is that not correct?

Thanks!

Re: Select Distinct from Group

jwhite — Mon, 09 Jan 2017 18:17:57 GMT

Nevermind. I think I've got it.

I appreciate all the help and insight!

Re: Select Distinct from Group

ballardw — Mon, 09 Jan 2017 21:56:24 GMT

If the other columns are numeric and amenable to statistic such as max or min (and few others) as an alternate approach:

proc summary data=have nway;

class col1 col2 col3 col4 ;

var var1 var2 var3;

output out=want(drop = _type_ _freq_) max= ;

run;

Re: Select Distinct from Group

LinusH — Tue, 17 Jan 2017 16:15:53 GMT

I think that @LaurieF nailed it with "I use Extract when I know that the transformation will remain a simple one".
Extract is easier to use and understand, so as long as Join is not simplified I think it has its place.
If you have only 1-1 mappings it's not that much of penalty to switch to join if you later find out that the Extract isn't enough.