Keeping the defined sortedby values in the metadata

[xxformat_com]

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;

[Patrick · Accepted solution]

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;

[xxformat_com]

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?

[Patrick]

---

@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

[xxformat_com]

When searching on the net, I could find three other values but I don't know if there is any other one.

• SK when using nodupkey in proc sort or distinct in proc sql
• SR when using nodup in proc sort
• O when removing the sorting

proc datasets lib = work nolist nodetails;
    modify class (sortedby = _NULL_);
    run;
quit;

[Patrick]

Thanks for sharing.

%macro testIt(param);
  proc sort data=sashelp.class out=class &param;
    by sex descending name;
  run;
  title "Proc Sort with &param";
  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);

[xxformat_com]

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

[Patrick]

@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...