How do I find the 4th record in SQL/ SAS PROC SQL?

1
votes

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

1
sqlsas

1 Answers

0
votes

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;