Does PROC SQL's "Select Distinct" always output in order?

Oligolas — Mon, 21 Feb 2022 16:54:59 GMT

Hi community,

Are the results of a PROC SQL's "Select distinct" always given in sorted order?

Or are there data arrangements (maybe with indexes?) that would/could affect the order of the results?

I can't find any documentation where the operations behind "Distinct" are explicitely described

I've performed some tests on indexes datasets but it seems that the results are always sorted.

(I've never questionned that, but I am doing now)

I'm not using any groups and the order variable I need is always the "distinct" variable.

data class(index=(sex));
set sashelp.class;
run;
proc sort data=class force;by sex name;run;

proc sql;
select distinct age
from sashelp.class
;
*same results as;
select distinct age
from class
;
quit;

I'd be grateful if you could share some piece of information on this.

Thanks

Re: Does PROC SQL's "Select Distinct" always output in order?

Quentin — Mon, 21 Feb 2022 20:50:04 GMT

Probably, but it's also not a good idea to assume that it always will.

Generally, SQL is a set language (as in logical sets), and order doesn't matter for sets. So the same query will sometimes reorder data and sometimes will not, depending on how the SQL optimizer decides to execute the query.

If you care about the order of the output data, you should always add an ORDER BY clause.

topic Re: Does PROC SQL's "Select Distinct" always output in order? in SAS Programming

Does PROC SQL's "Select Distinct" always output in order?

Re: Does PROC SQL's "Select Distinct" always output in order?