convert over partition into sas code

[alepage]

Here's the sql code:

select row_number() over (partition by MAIN_INSRR_CO_NBR, pol_typ_cd,pol_nbr order by TRM_NBR desc, TRM_VER_NBR desc) as NO, 
 aa.*
 from  table_1 aa

In a previous question, it was provided the anwser below:

data want;
set table1;
by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr;
if first.pol_nbr
then no = 1;
else no + 1;
run;

But how do we deal with the order by TRM_NBR desc, TRM_VER_NBR desc?
How do we include it into the dataset want ?


[Tom]

Read how to specify a BY statement.

[alepage]

Hello Tom, Thank you for the link to a by statement.

My question was how to convert the over partion sql statement into a sas code to obtain the same result.

select row_number() over (partition by MAIN_INSRR_CO_NBR, pol_typ_cd,pol_nbr order by TRM_NBR desc, TRM_VER_NBR desc) as NO, 
 aa.*
 from  table_1 aa

 In this over partion statement we have a by main_insrr_co_nbr, pol_typ_nbr order by trm_nbr des, trm_ver_nbr des.

In the example provided, we have 

data want;
set table1;
by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr;
if first.pol_nbr
then no = 1;
else no + 1;
run;

as you can see, the portion of the sql script, i.e., the order by trm_nbr des, trm_ver_nbr des is not included into the proposed SAS code.

Thank you very much for the conversion example.  But what do we do with the order by statement (sql) into the SAS code.  

proc sort data=table_1;
by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr descending TRM_NBR descending TRM_VER_NBR;
run;

then

 data sort;
set table_1;
by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr;
if first.pol_nbr
then no = 1;
else no + 1;
run;

or 

data sort;
set table_1;
by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr descending TRM_NBR descending TRM_VER_NBR;
if first.pol_nbr
then no = 1;
else no + 1;
run;

I don't understand the over partition by ..... order by statement into the sql script. Therefore, I dont know how to convert it to SAS code.  It is why I asking for help

[Tom · Accepted solution]

So find a source for what PARTITION BY does. Here is one https://www.sqlshack.com/sql-partition-by-clause-overview/

So you have:

partition by MAIN_INSRR_CO_NBR, pol_typ_cd,pol_nbr
  order by TRM_NBR desc, TRM_VER_NBR desc

Basically you are saying to treat observations with the same values of MAIN_INSRR_CO_NBR, pol_typ_cd and pol_nbr as separate subsets (partitions).  And within each of those subsets to sort the observations using descending TRM_NBR and descending TRM_VER_NBR.

So your BY statement is going to use ALL of those variables.  For the first 3 it does not really matter to SQL whether they are ordered ascending or descending.  But for the last two it does.  Otherwise you will number the observations in the opposite order.

So use this BY statement:

by MAIN_INSRR_CO_NBR pol_typ_cd pol_nbr
   descending TRM_NBR descending TRM_VER_NBR
;

And you want to restart the numbering when FIRST.POL_NBR is TRUE.

[alepage]

It is the first time for me that someone take the time to explain me what does over partion. It is clear for me now. Thank you very much.