topic Re: For each variable in the SAS table I want to derive nth maximum value in SAS Programming

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

Saurabh_Rana — Thu, 14 Oct 2021 09:25:37 GMT

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?

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

PeterClemmensen — Thu, 14 Oct 2021 08:14:50 GMT

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

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

Saurabh_Rana — Thu, 14 Oct 2021 09:31:02 GMT

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.

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

yabwon — Thu, 14 Oct 2021 09:38:24 GMT

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

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

acordes — Thu, 14 Oct 2021 09:42:38 GMT

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];

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

Saurabh_Rana — Thu, 14 Oct 2021 10:08:22 GMT

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

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

yabwon — Thu, 14 Oct 2021 10:09:54 GMT

var _all_;

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

Saurabh_Rana — Thu, 14 Oct 2021 10:31:27 GMT

Thank you so much! The code worked

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

acordes — Thu, 14 Oct 2021 10:31:40 GMT

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;

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

Saurabh_Rana — Thu, 14 Oct 2021 10:32:23 GMT

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

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

PeterClemmensen — Thu, 14 Oct 2021 11:12:34 GMT

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;

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

PaigeMiller — Thu, 14 Oct 2021 11:15:39 GMT

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.

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

Saurabh_Rana — Thu, 14 Oct 2021 11:21:53 GMT

Can you please share sample code

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

PaigeMiller — Thu, 14 Oct 2021 11:25:34 GMT

@Saurabh_Rana wrote:
Can you please share sample code

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