Initially I tried including a sample SAS Table here but I can’t get the script to line break where it should – Appreciate if you can let me know how to manually insert the line break when typing in the text editor, thanks
My question:
For SAS PROC SQL – How can I return the 4th highest record (that is, after sorting – like a list of clietns/assets)? (i.e. I do not want 1st,2nd or 3rd, thus not using outobs=)
Similarly, what is the syntax for SQL (which might be different from SAS PROC SQL) – I believe it can utilize the LIMIT argument?
Thank you for the help
Advertisement
Answer
In data step you can use the automatic _N_
variable. For SQL, there is an undocumented function in SAS called monotonic()
. It gives you the record number. Here are a couple ways to do what you need:
data blah; letter = "ay "; number = 2; output; letter = "bea"; number = 3; output; letter = "see"; number = 42; output; letter = "Dea"; number = 99; output; letter = "Eee"; number = 1; output; run; data new; set blah; theN = _N_; * if you want to save the record number; if theN = 4; run; proc sql; create table new2 as select blah.*, monotonic() as theN from blah having (theN = 4) ; quit;
If the data is not sorted you can order it using an order by clause:
proc sql; create table ordered as select * from blah order by letter; create table new2 as select ordered.*, monotonic() as theN from ordered having (theN = 4); drop table work.ordered; quit;
If the data is numeric you can also use proc rank.
data blah; letter = "ay "; number = 2; output; letter = "bea"; number = 3; output; letter = "see"; number = 42; output; letter = "Dea"; number = 99; output; letter = "Eee"; number = 1; output; run; proc rank data = blah out = new3 (where = (theRank = 4)); var number; ranks theRank; run;