topic Re: Keeping the defined sortedby values in the metadata in SAS Programming

Keeping the defined sortedby values in the metadata

xxformat_com — Sun, 17 Jul 2022 12:15:03 GMT

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;

Re: Keeping the defined sortedby values in the metadata

Patrick — Sun, 17 Jul 2022 14:41:21 GMT

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;

Re: Keeping the defined sortedby values in the metadata

xxformat_com — Thu, 21 Jul 2022 09:51:33 GMT

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?

Re: Keeping the defined sortedby values in the metadata

Patrick — Thu, 21 Jul 2022 11:06:25 GMT

@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

Re: Keeping the defined sortedby values in the metadata

xxformat_com — Sat, 23 Jul 2022 13:55:25 GMT

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;

Re: Keeping the defined sortedby values in the metadata

Patrick — Sat, 23 Jul 2022 13:18:50 GMT

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);

Re: Keeping the defined sortedby values in the metadata

xxformat_com — Mon, 03 Oct 2022 03:50:31 GMT

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

Re: Keeping the defined sortedby values in the metadata

Patrick — Mon, 03 Oct 2022 07:35:11 GMT

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