topic convert over partition into sas code in SAS Enterprise Guide

convert over partition into sas code

alepage — Thu, 18 Jul 2024 13:59:24 GMT

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 ?

Re: convert over partition into sas code

Tom — Thu, 18 Jul 2024 15:00:06 GMT

Read how to specify a BY statement.

Re: convert over partition into sas code

alepage — Thu, 18 Jul 2024 15:29:26 GMT

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;

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

Re: convert over partition into sas code

Tom — Thu, 18 Jul 2024 15:55:27 GMT

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.

Re: convert over partition into sas code

alepage — Thu, 18 Jul 2024 16:12:03 GMT

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.