How to select the first observation in each group, ordered by specific variables

[Romeo1]

proc sql;
create table a as 
select rank,      
              pos_id,       
              c_estado,     
              d_estado,     
              c_cartao_sup, 
              c_ndc_sup,    
              c_orgao_resp 
from (select row_number() over (partition by pos_id order by c_estado asc, c_cartao_sup desc) as rank, 
                            pos_id,      
              c_estado,     
              d_estado,     
              c_cartao_sup, 
              c_ndc_sup,    
              c_orgao_resp 
            from  f_agreg_mens_pos)x
where rank=1;
run;

Editor's Note: The title was changed to more generally reflect the goal. 

[PeterClemmensen]

See if you can use the 'translation' in the example below by converting to subqueries

https://stackoverflow.com/questions/43191746/sql-to-proc-sql-partition-by-alternative-min-case

[Romeo1]

Hi, I can not translate by converting to subqueries, it's confused. There is no way to do row_number() over (partition by ) in SAS Studio?

[Kurt_Bremser · Accepted solution]

Try this:

proc sort data=f_agreg_mens_pos;
by pos_id c_estado descending c_cartao_sup;
run;

data want;
rank = 1;
set f_agreg_mens_pos;
by pos_id;
if first.pos_id;
keep
  rank    
  pos_id
  c_estado
  d_estado
  c_cartao_sup
  c_ndc_sup
  c_orgao_resp
;
run;

If I read that code correctly, you want the top observation according to the sort order.