Data Set Sorted By Information

#UPDATE# Please see the comments for a better way to get the sorted by information from a data set! #UPDATE#

The other day a colleague mentioned that it was not possible to get the sort information for a data set from the dictionary tables. Intrigued, I
took up the challenge. While it's true that you cannot get the sorted by information directly, it is possible to get the necessary information
and put it together.

First of all, a little test data set:

data myData;
  input key1 nonKey keyB key3;
cards; 
1 2 6 3
1 5 5 7
2 7 4 4
3 3 5 9
4 5 3 2
1 5 4 6
3 3 9 8
5 5 7 9
6 6 4 3
3 3 3 6
;

I chose the names key1, nonKey, keyB, and key3 to make sure I wasn't getting the variables in alphabetical order
and mistaking it for the sort order.

Now sort the data set:

proc sort data = myData;
  by key3 descending keyB key1;
run;

It is important to make sure this works with descending sorts. Also notice the
variables are in a different order than in the data step so we don't confuse any artifact of creation order with sort order.

And now finally the code that will report the sort order. I used SQL and a data _null_ step to get the information then I just write it to the log. Originally I was just trying to see if it could be done, but just writing it to the log is not the most useful. Now that I've seen it works, I can rewrite it as all macro code using the vtables and %sysfunc() calls. That would allow me to make it a "function" style macro which returns a value to be used within code. Maybe tomorrow or next week...

%macro getSortedByVars(lib=,mem=);
%* This macro will write the sort order of a data set into the log;
%* If there is no sort on the data set it returns a blank;
%* it takes two parameters: the library and the name of the data set;
%* June 2008, Stephen Philp datasteps.blogspot.com/pelicanprogramming.com;
%let lib = %upcase(&lib);
%let mem = %upcase(&mem);

proc sql;
  create table keys
  as select name, sortedBy, case 
                    when (sortedBy<0) then 'DESCENDING'
     when (sortedBy>1) then ' '
      end as prefix,
      case 
                    when (sortedBy<0) then abs(sortedBy)
     else sortedBy
      end as sortOrder
from dictionary.columns
  where libname ="&LIB"    and
        memname = "&MEM" and
  sortedBy ne 0
  order by sortOrder;
quit;

%* now pack up those values from the keys table;
data _null_;
  length value $32767; 
  if 0 then set keys nobs=n;
  do i = 1 to n;
    set keys point=i;
 value = catX(' ', value, prefix, name);
  end;
  call symput('sortedBy',trim(value));
  stop;
run;

%put &sortedBy;
%mend getSortedByVars;

%getSortedByVars(lib=work,mem=mydata);