Hi,
The two examples below gives the same result: sortedby includes the sorting order.
But when the dataset is modified (add the commented dataset to test it - additional variable could also be added - so proc copy would not be sufficient), the sorted order is lost.
Is this meant to be. Is there a way to avoid it?
proc sort data=sashelp.class out=class;
by sex descending name;
run;
/*
data class;
set class;
run;*/
proc sql;
select name, sortedby
from dictionary.columns
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
*---------------------------------------------;
data class (sortedby=sex descending name);
set class ;
run;
/*
data class;
set class;
run;*/
proc sql;
select name, sortedby
from dictionary.columns
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
The sortedby option is a writable data set attribute that doesn't ensure that the data is actually sorted this way.
Creating a table using Proc Sort ensures that the table is sorted.
IF you know your table is sorted and you want SAS to set the attribute for verified sorted then use Proc Sort with the PRESORTED option. With this option SAS will first verify if a table is already sorted and only actually sort the table if not confirmed.
Below code should explain it.
I'm not sure where the sortedby option adds value to processing as SAS will only use more efficient processing (like in a Proc SQL) if the sort order is verified - which is a table attribute you can't write directly.
proc sort data=sashelp.class out=class;
by sex descending name;
run;
proc sql;
select memname, sorttype
from dictionary.tables
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
data class (sortedby=sex descending name);
set sashelp.class;
run;
proc sql;
select memname, sorttype
from dictionary.tables
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
proc sort data=work.class presorted;
by sex descending name;
run;
proc sql;
select memname, sorttype
from dictionary.tables
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
The sortedby option is a writable data set attribute that doesn't ensure that the data is actually sorted this way.
Creating a table using Proc Sort ensures that the table is sorted.
IF you know your table is sorted and you want SAS to set the attribute for verified sorted then use Proc Sort with the PRESORTED option. With this option SAS will first verify if a table is already sorted and only actually sort the table if not confirmed.
Below code should explain it.
I'm not sure where the sortedby option adds value to processing as SAS will only use more efficient processing (like in a Proc SQL) if the sort order is verified - which is a table attribute you can't write directly.
proc sort data=sashelp.class out=class;
by sex descending name;
run;
proc sql;
select memname, sorttype
from dictionary.tables
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
data class (sortedby=sex descending name);
set sashelp.class;
run;
proc sql;
select memname, sorttype
from dictionary.tables
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
proc sort data=work.class presorted;
by sex descending name;
run;
proc sql;
select memname, sorttype
from dictionary.tables
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
I see your point. Having the information in the dataset metadata does not ensure that it reflects the actual order of the data.
Are you aware of a SAS Online Documentation listing all possible values for SORTTYPE?
@xxformat_com wrote:
I see your point. Having the information in the dataset metadata does not ensure that it reflects the actual order of the data.
Are you aware of a SAS Online Documentation listing all possible values for SORTTYPE?
I'm not but to my knowledge it's only verified or not verified - S or W
When searching on the net, I could find three other values but I don't know if there is any other one.
proc datasets lib = work nolist nodetails;
modify class (sortedby = _NULL_);
run;
quit;
Thanks for sharing.
%macro testIt(param);
proc sort data=sashelp.class out=class ¶m;
by sex descending name;
run;
title "Proc Sort with ¶m";
proc sql;
select memname, sorttype
from dictionary.tables
where upcase(libname)='WORK' and
upcase(memname)='CLASS';
quit;
title;
%mend;
%testIt();
%testIt(nodupkey);
%testIt(noduprec);
%testIt(nouniquekey);
I've just found this documentation for S (strong) and W (weak):
https://support.sas.com/documentation/cdl/en/sclref/59578/HTML/default/viewer.htm#a000143464.htm
@xxformat_com Nice! Same information but links to a more recent SAS version. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/lefunctionsref/p048h4pczkr351n1pk558jo8hcc0.h...
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.