Find presorted variables from unknow sas dataset

[chinna0369]

Hi Team,

Is there any way to find the variable list of a sorted dataset?

Thanks,

Adithya

[chinna0369]

I mean by which variables the dataset is sorted by?

[PaigeMiller]

PROC CONTENTS will do this.

[chinna0369]

You mean "Alphabetic List of Variables and Attributes" in this section the number indicates sorting order of the dataset?

[PaigeMiller]

No that's not what I mean. The list of variables by which the data set is sorted is in a different section of the output. See this example, scroll all the way down to the bottom.

[chinna0369]

No, I think it displays only when Sorted section says "YES" in "The Contents Procedure" section. But In my case it says "NO".

[PaigeMiller]

So the data set was not sorted by SAS. It may have been created in a sorted order before it got into SAS (such as in Excel), in which case SAS does not know the sort variables.

[ballardw]

---

@chinna0369 wrote:
No, I think it displays only when Sorted section says "YES" in "The Contents Procedure" section. But In my case it says "NO".

---

Just how many variables are involved? Just one data set or will you have to do this repeatedly? Or should all of these sets, if multiple, be sorted the same?

Or look at the variables and sort in a way that makes sense for the following processing and don't worry about current order.

You may well spend more time trying to solve this, at least to me, extremely minor issue than it is worth.

[PaigeMiller]

If you are going to do this once or twice, the "eyeball method" — look at the data set with your own eyes — will do this. If you have to do this repeatedly, then there is no easy method.

[Tom]

Here is one way to find potential candidates for the top level sorting.

I leave it as an exercise how to generate this code for some arbitrary dataset.

data _null_;
  set sashelp.class end=eof;
  array incr [5] _temporary_ (5*1);
  array decr [5] _temporary_ (5*1);
  array names [5] $32 ('Name' 'Sex' 'Age' 'Height' 'Weight');
  if _N_>1 and lag(name)>name then incr[1]=0;
  if _N_>1 and lag(sex)>sex then incr[2]=0;
  if _N_>1 and lag(age)>age then incr[3]=0;
  if _N_>1 and lag(weight)>weight then incr[4]=0;
  if _N_>1 and lag(height)>height then incr[5]=0;
  if _N_>1 and lag(name)<name then decr[1]=0;
  if _N_>1 and lag(sex)<sex then decr[2]=0;
  if _N_>1 and lag(age)<age then decr[3]=0;
  if _N_>1 and lag(weight)<weight then decr[4]=0;
  if _N_>1 and lag(height)<height then decr[5]=0;
  
  if 0=max(of incr[*] decr[*]) then do;
    put 'NOTE: No sorting variables found.';
    stop;
  end;

  if eof then do i=1 to 5;
    if incr[i] then put 'NOTE: Sorting variable ' names[i] ;
    if decr[i] then put 'NOTE: Descending sorting variable ' names[i] ;
  end;
run;

[ErikLund_Jensen]

 @chinna0369 

I agree with @ballardw . Not worth the effort.

There is no way to determine the sort order from a data set that ihas not previously been sorted by SAS, so always us a Proc Sort in cases where sorting is required. If a data set has previously been sorted by SAS, the procdure is smart enough to omit sorting when data are already sorted in the wanted sequence, so It comes with no cost besides coding a couple of lines.

It is mandatory in good coding practice to make sure that a data set is sorted before any use that requires sorting, which means all Data Steps with first-last variables, retain, lag or merge, and most SAS Procedures, except Proc SQL. Always use a Proc Sort before these steps. 

The reverse applies to the order of variables. Don't put any effort in bringing variables in a specific order while preparing data. Leave that until you come to creating reports or exporting data to Excel.