raw.md

data class;
    set sashelp.class;
    keep name age sex weight;
run;

proc sort;
    by weight;
run;


title "Select highest weight overall";
proc sql outobs = 1;
    
    select name, weight
    from class
    order by weight desc
;quit;


title "Select second highest weight overall";
proc sql outobs = 1;
    
    select name, weight 
    from class
    where weight not in (select max(weight) from class)
    order by weight desc
;quit;

title "Select Nth highest weight";
%let n = 4;

proc sql;
    
    select distinct cls1.name, cls1.weight
    from class as cls1
    where (select count(distinct cls2.weight)
        from class as cls2
        where cls2.weight > cls1.weight
        ) = &n - 1;
quit;


title "Select highest weights by group";
proc sql;

    select sex, name, weight
    from class
    group by sex
    having weight = max(weight)
;quit;

title "Rank all weights";
proc sql; 
    select name, weight, (select count(distinct b.weight) 
            from class as b where b.weight >= a.weight) as Rank
    from class as a
    order by rank
;quit;

proc sql;
    select a.name, a.weight, count(b.weight) as rank
    from class as a, (select distinct weight
           from class
           ) as b
    where a.weight <= b.weight
    group by a.name, a.weight
    order by a.weight desc;
quit;


title "Select Top N weights by group";
proc sql; 
    select a.sex, a.name, a.weight, (select count(distinct b.weight) 
            from class as b where b.weight >= a.weight and a.sex = b.sex ) as rank 
    from class as a
    where calculated rank <= 3
    order by sex, rank
;quit;

import os
from sas7bdat import SAS7BDAT
from tabulate import tabulate

def set_dir():
    _sasDir = 'C:/Users/CHAOHU~1.CTR/AppData/Local/Temp/1/SAS Temporary Files/'
    sasDir = list(os.listdir(_sasDir))
    sasWorkDir = filter(lambda x : x[:3] == '_TD', sasDir)
    os.chdir(_sasDir + sasWorkDir[0]) # this has to changed every time


pandas.set_option('display.width', 1000)
set_dir()

##sasdata = [ x for x in os.listdir('.') if 'sas7bdat' in x]

header = []
table = []

with SAS7BDAT('{}'.format('class.sas7bdat')) as f:
    for row in f:
        if len(header) == 0:
            header = map(str, row)
        else:
            table.append(row)
table.sort(key = lambda x: x[3], reverse = True)

print tabulate(table, header, tablefmt="pipe")