Google SAS Search

Add to Google

Wednesday, June 04, 2008

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

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;
%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,
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;

%* 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);
call symput('sortedBy',trim(value));

%put &sortedBy;
%mend getSortedByVars;



  1. Stephen, nice bloggin'! Just one observation: if I try to copy-and-paste your code in my (or SAS) code editor, it appears as a single line. Luckily, my code editor has an indenter, so no big deal, but if you're using the SAS Enhanced Editor, you're out of luck. :-) You can use my Notebook to export your SAS code to HTML code, but Blogger may scrub HTML tags when you publish your blog post, as most blog hosts do. Regards, Audi

  2. I got a nice reply from Jason Secosky at the SAS Institute that the code I posted is not the best way to get the sorted by information from a data set. His suggestion is to use the attrC() function:
    data _null_;
    dsid = open('work.myData');
    sortedBy = attrc(dsid, 'sortedby');
    put sortedBy=;

    Much, much better than the mess I wrote! I don't know why I didn't think of the attrC() function? Probably because my colleague mentioned it in the context of dictionary tables, so off I went in that direction!