How to find Second largest number in a Column using PROC MEANS in SAS?

0
votes

i have been working on SAS since a while. i have created a dummy dataset as follows:

data try1;
infile datalines;
length Level $ 10;
input Variable $ Level $ Exposure;
datalines;
A A1 100
A A2 200
A A3 300
A Unknown 1000
B B1 70
B B2 20
B B3 30
B B4 40
B Unknown 100
C C1 200
C C2 100
C C3 80
C Unknown 50
;
Run;

After successful completion of creating a dataset now i tried to find out the largest number in each class of 'Variables' and this has been done by the PROC MEANS procedure. the code for the same is as follows:

proc means data=try1 noprint;
Class Variable;
id Level;
var Exposure;
output out = try2 (drop = Level _TYPE_ _FREQ_)
maxid(Exposure(Level)) = max_factor 
max=;
run;

I was able to get the correct output by the same. But now i want to know the second largest number in each class of 'Variables' and certainly i need an OUTPUT like this:

Max_Exposure    Sec_Max_Exposure    Output
  Unknown            A3               A3
  Unknown            B1               B1
     C1              C2               C1

basically i need to eliminate the Unknown 'Level' so that i can get a desired output column.

Can anyone help me out with the problem using PROC MEANS statement.

Regards

2
sas
I am intended to do the same on R also. – desmond.carros

2 Answers

1
votes

I think this is what you want, but it does not match the OUTPUT NEEDED that you show. You're cryptic exploitation does not help.

This doesn't have any special treatment of EXPOSURE values that are tied.

data try1;
infile datalines;
length Level $ 10;
input Variable $ Level $ Exposure;
datalines;
A A1 100
A A2 200
A A3 300
A Unknown 1000
B B1 70
B B2 20
B B3 30
B B4 40
B Unknown 100
C C1 200
C C2 100
C C3 80
C Unknown 50
;
Run;
proc print;
   run;
proc summary data=try1 nway;
   where level ne: 'Unk';
   class variable;
   output out=max2(drop=_type_)
      idgroup(max(exposure) out[2](exposure level)=);
   run;
proc print;
   run;

enter image description here

0
votes

You could do it in a single datastep...

data want ;
  set have ;
  by Variable ;

  length maxid1 maxid2 $10. 
  retain max1 max2 . maxid1 maxid2 '' ;

  if first.Variable then call missing(of max:) ;

  if Exposure > max1 then do ;
    max2 = max1 ;
    maxid2 = maxid1 ;
    max1 = Exposure ;
    maxid1 = Level ;
  end ;

  if last.Variable then output ;
run ;