Clark and ballrdw, you're right that the "sashelp.class" is too simple of an example.
In my data I have duplicate records and "name" is spelled in very-mixed case.
I really want to take distinct and sort by upper(name)
Thank you for suggesting to avoid the union... and to sort instead
Instead of stacking males on top of females, I can sort... but only if it's a hidden sort inside DISTINCT
I wish I could do this:
proc sql outobs=100;
select name
into :var_list separated by ','
from (select distinct Sex, upper(name), name
from sashelp.class
where age>10
order by Sex desc, upper(name)
)
;quit;
Unfortunately, SAS doesn't understand the ORDER BY clause.
Fortunately, I can rely on the free sort provided by DISTINCT, and I can avoid the union, so I'll think I'll go with this:
proc sql outobs=100;
select name
into :var_list separated by ','
from (select distinct ifn(Sex='M',0,1), upper(name), name
from sashelp.class
where age>10
)
;quit;
I accepted Tom's answer as the solution because it sounds like a theoretical thing that I need to remember for the future and because it answers my original question about why ORDER BY doesn't work.
... View more
