Hi Guys,
I am trying Nth max value using datastep if same nth maxvalues in row a dataset
/*Using proc rank procedure nth maxvalues in a row */
proc rank
data=sashelp.class
ties=dense descending
out=want(where=(number_rank=2))
;
var age;
ranks number_rank;
run;
data rank;
set sashelp.class;
run;
proc sort data=rank;
by descending age;
run;
data ranking;
set rank;
by descending age ;
retain ranks;
if first.age then ranks+1;
proc print noobs;
run;
data maxcount ;
set ranking (obs=5);
if _n_ =2 then output;
proc print noobs;
run;
proc rank
data=sashelp.class
ties=dense descending
out=want(where=(number_rank=2))
;
var age;
ranks number_rank;
run;
I want required output
Name
|
|
|
|
|
|
Janet | F | 15 | 62.5 | 112.5 | 2 |
Mary | F | 15 | 66.5 | 112 | 2 |
Ronald | M | 15 | 67 | 133 | 2 |
William | M | 15 | 66.5 | 112 | 2 |
For efficiency, combine the first two steps:
proc sort
data=sashelp.class
out=rank
;
by descending age;
run;
Then, define the ranks:
data want;
set rank;
by descending age;
rank + (first.age); * SUM statement implies RETAIN;
run;
I leave it as a simple exercise to filter for rank = 2 in the second step.
Edit: added the DESCENDING keyword in the DATA step.
For efficiency, combine the first two steps:
proc sort
data=sashelp.class
out=rank
;
by descending age;
run;
Then, define the ranks:
data want;
set rank;
by descending age;
rank + (first.age); * SUM statement implies RETAIN;
run;
I leave it as a simple exercise to filter for rank = 2 in the second step.
Edit: added the DESCENDING keyword in the DATA step.
proc sort
data=sashelp.class
out=rank
;
by descending age;
run;
data want;
set rank ;
by descending age;
rank + (first.age); * SUM statement implies RETAIN;
if rank=2 ; /*fIlter Nth rank using if statement Note where statement cannot work here because assingnment variable rank only work with IF statement*/
proc print noobs;
run
Thank you,
Kurt for your solution
In this case you merely need to keep a running record of the top 2 values of AGE as you read sashelp.class. Every time an obs is encountered that matches the Nth largest age (2nd largest), stick it in a hash object.
When you reach the end of sashelp.class, you know the Nth largest age value, and you can use that to output data from the hash object, filtering on age=Nth highest value. Given the hash object is declared to be sorted by the same var (name) as sashelp.class, the resulting dataset has the original data order:
data _null_;
set sashelp.class end=end_of_class;
retain age1 age2 ;
if _n_=1 then do;
declare hash h (dataset:'sashelp.class (obs=0)',ordered:'A');
h.definekey('name');
h.definedata(all:'Y');
h.definedone();
end;
if age>=age2 then h.add();
if age>age1 then do; age2=age1; age1=age; end; else
if age>age2 then age2=age;
if end_of_class then h.output(dataset:cats('want (where=(age=',age2,'))'));
run;
If you're willing to read the dataset twice in a single data step, then the simplest code would be:
data want;
set sashelp.class (in=first_pass) sashelp.class (in=second_pass);
array _age{2} _temporary_;
if first_pass=1 and age>_age{1} then do;
_age{1}=age;
call sortn(of _age{*}); /* Results in ascending sort of _age{1} and _age{2} */
end;
if second_pass and age=_age{1};
run;
Thank You,
Mkeintz for your solution
in your tricky code how to filter Nth max values suppose i want 5 max value where i have to change in this code
@BrahmanandaRao wrote:
Thank You,
Mkeintz for your solution
in your tricky code how to filter Nth max values suppose i want 5 max value where i have to change in this code
Do you want the 5th largest age, or all observations with the 5th largest or larger?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.