BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Saurabh_Rana
Obsidian | Level 7

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?

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

13 REPLIES 13
PeterClemmensen
Tourmaline | Level 20

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

Saurabh_Rana
Obsidian | Level 7

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
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Saurabh_Rana
Obsidian | Level 7
In proc transpose step how can I select all variables in the table
yabwon
Onyx | Level 15
var _all_;
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Saurabh_Rana
Obsidian | Level 7
Thank you so much! The code worked
acordes
Rhodochrosite | Level 12

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;

a1.png

Saurabh_Rana
Obsidian | Level 7
If there are repeated numbers in the column then do they get counted or not?
acordes
Rhodochrosite | Level 12

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
Tourmaline | Level 20
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
Diamond | Level 26

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.

--
Paige Miller
Saurabh_Rana
Obsidian | Level 7
Can you please share sample code

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1360 views
  • 2 likes
  • 5 in conversation