topic Re: How to select the first observation in each group, ordered by specific variables in SAS Programming

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

Romeo1 — Thu, 30 Jun 2022 17:14:16 GMT

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.

Re: How to convert the code with row_number() over (partition by ) in SAS Studio

PeterClemmensen — Wed, 25 Nov 2020 08:00:20 GMT

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

Re: How to convert the code with row_number() over (partition by ) in SAS Studio

Romeo1 — Mon, 30 Nov 2020 15:10:28 GMT

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?

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

Kurt_Bremser — Thu, 30 Jun 2022 17:15:53 GMT

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.