topic Re: Find presorted variables from unknow sas dataset in SAS Programming

Find presorted variables from unknow sas dataset

chinna0369 — Sun, 09 Apr 2023 14:59:53 GMT

Hi Team,

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

Thanks,

Adithya

Re: Find presorted variables from unknow sas dataset

chinna0369 — Sun, 09 Apr 2023 15:00:42 GMT

I mean by which variables the dataset is sorted by?

Re: Find presorted variables from unknow sas dataset

PaigeMiller — Sun, 09 Apr 2023 15:03:28 GMT

PROC CONTENTS will do this.

Re: Find presorted variables from unknow sas dataset

chinna0369 — Sun, 09 Apr 2023 15:05:32 GMT

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

Re: Find presorted variables from unknow sas dataset

PaigeMiller — Sun, 09 Apr 2023 15:15:03 GMT

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.

Re: Find presorted variables from unknow sas dataset

chinna0369 — Sun, 09 Apr 2023 16:14:56 GMT

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

Re: Find presorted variables from unknow sas dataset

PaigeMiller — Sun, 09 Apr 2023 16:38:30 GMT

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.

Re: Find presorted variables from unknow sas dataset

Tom — Sun, 09 Apr 2023 21:24:20 GMT

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;

Re: Find presorted variables from unknow sas dataset

ballardw — Sun, 09 Apr 2023 22:50:56 GMT

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

Re: Find presorted variables from unknow sas dataset

PaigeMiller — Mon, 10 Apr 2023 09:54:52 GMT

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.

Re: Find presorted variables from unknow sas dataset

ErikLund_Jensen — Mon, 10 Apr 2023 11:37:21 GMT

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