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
- Cheers -
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.