Sort Order of Datasets

Question

Is there any inherent method in SAS to find the sort order of a dataset?

Answer 1

If the data has indeed been "sorted", yes. There is a small table concerning sort information at the bottom of the output produced by proc contents.

Datasets that have been built with data that was already in some kind of sort order may not have this information attached to them and you will need to begin to explore the data to determine its order.

Answer 2

I'm assuming that the problem you are solving is trying to determine whether a dataset needs to be sorted in a programmatic manner. The best solution we've used is to simply use proc sort when in doubt.

Of course you might say, that's overhead and processing.. Well yes, but if the dataset is already sorted correctly, proc sort with know it and let your code move on with minimal processing. It has the "if sorted then move on" logic built in.

If this isn't the problem you are trying to solve, elaborate and we'll see if we can help.

Answer 3

As Sassy says, the only way SAS knows if a data set is sorted is if it did the sorting, or if you explicitly tell it the sort order. If you haven't done either of these steps, it will have no idea if the data is in any type of order.

I like AFHood's idea of just trying to sort it. If SAS knows it is sorted that way it will just tell you and won't do it again.

NOTE: Input data set is already sorted, no sorting done

Here are some other ideas for investigating data sorting...Enjoy.

If you just want to look at it manually, you can use proc contents data=libname.data;run; and look at the output. There is an attribute called sorted. If you are using the windowing mode you can right click on the data set in the explorer and choose properties, then click the details tab and see the sortedby values.

For a programmatic testing approach, you can use an output data set from proc contents. The sorted and sortedby columns will tell you if the data set is sorted and which variable it is sorted by. Try it by running the code below.

/* In an unsorted data set, proc contents will give missing values
   for the sorted and sortedby columns of its output data */
proc contents data=sashelp.class out=class_contents noprint;run;
proc print data=class_contents;
  var memname name sorted sortedby;
run;

/* Now sort and observe the changes in the sorted and sortedby columns */
proc sort data=sashelp.class out=class_sorted; by name;run;
proc contents data=class_sorted out=class_sorted_contents;run;
proc print data=class_sorted_contents;
  var memname name sorted sortedby;
run;`enter code here`

Answer 4

If sas didn't sort the data but you think it might be sorted, you can try to process it as though it's sorted and deal with the errors that may or may not occur as a result.

* This works, swap some values to see how an error looks;
data foo;
input height;
cards;
1
2
3
4
;
run;

data _null_;
set foo;
by height;
run;

Error states can be detected and reset in macro, but that approach is likely to get messy. More info on that here

Answer 5

You can use the Attrc function.

doco at http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000147794.htm

it goes something like the following

data _null_;
   dsid=open("work.a","i");
   sortby=attrc(dsid,"SORTEDBY");
   put  sortby=;
   rc=close(dsid);
run;