For each variable in the SAS table I want to derive nth maximum value

[Saurabh_Rana]

I have a SAS table with more than 100 columns like this->

A     B     C     D     E      F

8      3      7      1      3      3

.       .       .        .        .      .

.       .       .        .        .      .

.       .       .        .        .      .

Now I want to create a new table where against each variable nth maximum value is mentioned like this->

Variable Name       nth Max Value

A                                  21

B                                  45

C                                  32

D                                  64

E                                  13

.                                     .

.                                     .

How can I derive such a table?

[PeterClemmensen]

Do you want just 1 column like 'Nth max variable' ?

[Saurabh_Rana]

I want two columns, in the first column, all the variables names will be stored and in the second column, the nth max value for each variable (In the first column) will be stored.

[yabwon · Accepted solution]

Like that:

data  have;
  input A B C D E F;
cards;
8 3 7 1 3 3
23 4 5 267 7 84 2
0 11 4 6 9 11
;
run;

proc transpose data = have out = have1;
  var A--F;
run;

data want;
  set have1;
  array _C_{*} col:;
  n = 2; /* select second largest */
  nthValue = LARGEST(n, of _C_[*]);
  keep _NAME_ nthValue;
run;
proc print data = want;
run;

Bart

[Saurabh_Rana]

In proc transpose step how can I select all variables in the table

[yabwon]

var _all_;

[Saurabh_Rana]

Thank you so much! The code worked

[acordes]

Just for fun in IML

proc iml;
use have1;
read all var _num_ into x;
read all var {"_name_"} into vary;
close;

nth_highest=2;
nth_maxy=j(nrow(x),1,.);
nth_maxy_alt=nth_maxy;
do i=1 to nrow(x);
x_temp=x[i,]`;
call sort(x_temp,1,1);
nth_maxy_alt[i]=x_temp[nth_highest];
nth_maxy[i]=setdif(x[i,], x[i,<>]) [,<>]; /*works for the second highest*/
end;

print x nth_maxy nth_maxy_alt vary;

[Saurabh_Rana]

If there are repeated numbers in the column then do they get counted or not?

[acordes]

IML solution:

DATA TEST;

FORMAT VAR1CHAR $3.;
FLAG=0;
DO I=1 TO 100;
IF 1=1 THEN DO;
var1char = COMPRESS(CATX('', IFC(^FLAG, byte(I+64), CATX('', BYTE(64 + FLAG), byte(I+64 + IFN(FLAG=0, 0, -FLAG*26)))))) ;
FLAG=(MOD(I,26)=0)+FLAG;
OUTPUT;
END;
END;

RUN; 

proc transpose data=test out=want1( drop=_name_);
id var1char;
var flag;
run;

data want;
set want1;
format temp2 $500.;
array temp {100} _temporary_ (1 : 100);
array filler {100} _numeric_;
s = 123;
do _N_ = 1 to 10;
      call ranperm(s, of temp [*]);
temp2 = catq('d', '|', of temp [*]);
do port=1 to 100;
filler(port)=input(scan(temp2, port,  "|"), best12.);
end;
output;
end;
drop port temp2;
run;

proc iml;
use want;
read all var _all_ into x [colname=varname];
close;

maxy=x [<>,];

print maxy [c=varname];

[PeterClemmensen]

data  have;
  input A B C D E F;
cards;
8 3 7 1 3 3
23 4 5 267 7 84 2
0 11 4 6 9 11
;

data want_hoh(keep = v n);

    set have end = z;
    array nn _numeric_;

    if _N_ = 1 then do;
        dcl hash hh ();
        hh.definekey ("_i_");
        hh.definedata ("h", "i");
        hh.definedone ();
        
        do over nn;
            n = nn;
            dcl hash h (multidata : "Y", ordered : "D");
            h.definekey ("n");
            h.definedone ();
            dcl hiter i("h");
            hh.add();
        end;
    end;
    
    do over nn;
        hh.find();
        h.add (key:nn, data:nn);
    end;
    
    if z then do over nn;
        v = vname(nn);
        hh.find();
        
        do _N_ = 1 by 1 while (i.next() = 0 & _N_ < 2);
        end;
        
        output;
        
        rc = i.last();
        rc = i.next();
    end;
    
run;

[PaigeMiller]

The problem is simple if you use PROC RANK. In addition, unlike the other methods presented, PROC RANK gives you options on how to handle ties.

[Saurabh_Rana]

Can you please share sample code

[PaigeMiller]

---

@Saurabh_Rana wrote:
Can you please share sample code

---

Example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/n18s1uw2tqvtxdn1chay1qq1jke6.htm