SAS CODE to order variables Means from highest to lowest mean

Question

I can't do the SAS code to order a set of variables means from highest to lowest.

I already got the means of each variables, but I can't get SAS to order the variables from highest to lowest mean.

I already tried:

proc means data=stats std mean; 
  var x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 x16 x17 x18 x19 x20 x21 x22 x23 x24 x25    x26 x27 x28 x29 x30 x31 x32 x33 x34;
  output out=summaryStats mean=MeanVar std=stdVar;
run;

proc sort data=stats;
  by descending MeanVar;
run;

How do I convert the means into variables? or how can I order them from highest to lowes?

Answer 1

If I understand well you have 34 variables, X1-X34, and you want to sort them by their means. Your current code will put all 34 means on the same record, which you could then loop through via an array. Alternatively you could do something like this:

/* Transpose to long format (or use PROC TRANSPOSE) */
data tpose;
   set stats;
   array arr_x x:;
   do over arr_x;
      VARNUM = sum(VARNUM, 1);
      VARVAL = arr_x;
      output;
   end;
run;

proc sort data=tpose; by varnum; run;

proc means data=tpose mean std;
   by varnum;
   var varval;
   output out=summaryStats mean=meanVar std=stdVar;
run;

/* Put summaryStats VARNUM in order of highest to lowest mean. */
proc sort data=summaryStats; by descending meanVar; run;

If you want to rename the variables to be in order of their means it's as simple as back-transposing this dataset. You could also use CALL SORT to do all of this in a single datastep but it will be a bit more complicated, especially if you want to include other statistics such as your standard deviation.

edit: Oh, your code doesn't actually put all 34 means on the same record, it pools all 34 variables into one mean. Keep these separate by specifying different names for your output variable, e.g. output out=summaryStats mean=meanVar1-meanVar34;.

Answer 2

Here is one way to reorder the columns based on the column means descending.

Example:

The stackodsoutput option is used to obtain the mean of each variable in a shape that can be sorted. Macro is used to store the variable names so they can be used in a subsequent DATA step.

data have;
  call streaminit (20231015);
  do id = 1 to 10;
    array x x1-x34;
    do over x;
      x = _i_ + rand('integer',1, 20);
    end;
    output;
  end;
run;

proc means print data=have std mean stackodsoutput; 
  var x1-x34;
  ods output summary=means;
run;

proc sql noprint;
  select variable into :newcolumnorder separated by ' '
  from means
  order by mean desc;


%put NOTE: &=newcolumnorder;

data want;
  retain id &newcolumnorder;
  set have;
run;

The intermediate summary data used to determine the new column order based on mean.

And the resultant data set with the new column ordering

Answer 3

As long as the stats you want are in the set N, MiN, MAX, MEAN and STD you can use the default OUTPUT dataset format and just transpose it so you can then sort by the MEAN.

Let's do it for SASHELP.CLASS which should be available on all SAS installations.

proc summary data=sashelp.class;
  var weight height age;
  output out=stats;
run;

proc print;
run;

proc transpose data=stats out=want;
  by _type_ _freq_ ;
  id _stat_;
run;

proc print;
run;

proc sort;
  by mean;
run;

proc print;
run;

Result :