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.
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;
@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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.