How can I look for a variable value and return the value of another variable

[Saidaral]

Hi, I'm using SAS 9.4. I have a pretty large data set (Millions of data) and I have to find the max value in the VAR1 and then return the value of VAR2, for that max value I found before. Both variables are in the same data set Call B1.

I already have created a new data set with the max value of VAR1 (Call US)

my code for the max values of VAR1 was: 

Proc means data=WORK.B1 max nway;

var VAR1;

OUTPUT out = US (drop= _TYPE_ _FREQ_)

Max = / autoname;

run;

Thank you so much for your help. 

[PeterClemmensen]

Hi and welcome to the SAS Communities 🙂

Here is an SQL Approach based on the SASHELP.CLASS data set

proc sql;
   create table want as
   select name
   from sashelp.class
   having height=max(height);
quit;

So in your case, it will be something like this

proc sql;
   create table want as
   select var2
   from B1
   having var1=max(var1);
quit;

[PaigeMiller]

---

@Saidaral wrote:

Hi, I'm using SAS 9.4. I have a pretty large data set (Millions of data) and I have to find the max value in the VAR1 and then return the value of VAR2, for that max value I found before. Both variables are in the same data set Call B1.

I already have created a new data set with the max value of VAR1 (Call US)

my code for the max values of VAR1 was: 

Proc means data=WORK.B1 max nway;

var VAR1;

OUTPUT out = US (drop= _TYPE_ _FREQ_)

Max = / autoname;

run;

Thank you so much for your help. 

---

In PROC MEANS, use the MAXID option of the OUTPUT statement.

OUTPUT out = US (drop= _TYPE_ _FREQ_) 
    Max = var1_max 
    maxid(var1(var2))=var2_at_max_var1;

[hashman]

It can be done in a single-pass DATA step:

data have ;                         
  input var1 var2 ;                 
  cards ;                           
10 20                               
18 21                               
11 22                               
19 23                               
17 24                               
12 25                               
16 26                               
13 27                               
15 28                               
14 29                               
run ;                               
                                    
data want (drop = _:) ;             
  do until (z) ;                    
    set have end = z ;              
    if var1 <= _max1 then continue ;
    _max1 = var1 ;                  
    _var2 = var2 ;                  
  end ;                             
  var1 = _max1 ;                    
  var2 = _var2 ;                    
run ;                               

Kind regards

Paul D.