topic row_number() OVER ( partition BY in SAS Programming

row_number() OVER ( partition BY

rohitdev_ds — Thu, 12 Nov 2020 06:13:55 GMT

Hello,

I am trying to convert a SQL code in SAS using SQL passthrough. The code runs well in SQL and produces data but does not rut produce any data in SAS. Here is the code

PROC SQL;
connect using XXXX;
create table TEST1 as select * from connection to XXXX
(
SELECT
RP.REPORTING_DATE_PK,
RP.REPORTING_PERIOD_END_DATE as 'Report Date',
C.CONTRACT_ID as 'Contract ID',
IP.PORTFOLIO_CODE as 'Portfolio',
CG.COHORT as 'Cohort',
CG.PROFITABILITY_AT_INCEPTION as 'Initial Profitability',
CG.MEASUREMENT_APPROACH as 'Calculation_Approach',
CG.GROUP_CODE as 'Group ID'

FROM ABC.CONTRACT_GROUP_REFERENCE CGR
LEFT JOIN (SELECT A.REPORTING_PERIOD, A.REPORTING_DATE_PK, REPORTING_PERIOD_END_DATE,A.REPORTING_MONTH,
ROW_NUMBER() OVER ( partition BY A.REPORTING_PERIOD ORDER BY A.REPORTING_MONTH) AS MONTH_SEQ
FROM ABC.REPORTING_DATE a, con.REPORTING_PERIOD b
WHERE a.REPORTING_PERIOD = b.REPORTING_PERIOD
AND a.RECORD_CURRENT_FLAG = 'Y') RP ON RP.REPORTING_DATE_PK = CGR.REPORTING_DATE_FK
LEFT JOIN ABC.CONTRACT C ON C.CONTRACT_PK = CGR.CONTRACT_FK AND C.REPORTING_DATE_FK = RP.REPORTING_DATE_PK
LEFT JOIN ABC.CONTRACT_GROUP CG ON CG.GROUP_PK = CGR.GROUP_FK AND CG.REPORTING_DATE_FK = RP.REPORTING_DATE_PK
LEFT JOIN ABC.INSURANCE_PORTFOLIO IP ON IP.PORTFOLIO_PK = CG.PORTFOLIO_FK

WHERE RP.REPORTING_PERIOD = '2020Q3'
AND RP.MONTH_SEQ = 1
AND IP.PORTFOLIO_CODE = 'CI');

QUIT;

Can someone help? how should i convert this code to SAS to get data? I guess row_number() partition by does not work here.

thank you

Re: row_number() OVER ( partition

ChrisNZ — Thu, 12 Nov 2020 07:18:08 GMT

This is Oracle code right, not SQL Server?

Are you saying that the code in blue give a different result when run in Oracle and when run as below in a SAS pass-through query?

PROC SQL;
connect using XXXX;
create table TEST1 as select * from connection to XXXX
(SELECT
RP.REPORTING_DATE_PK,
RP.REPORTING_PERIOD_END_DATE as 'Report Date',
C.CONTRACT_ID as 'Contract ID',
IP.PORTFOLIO_CODE as 'Portfolio',
CG.COHORT as 'Cohort',
CG.PROFITABILITY_AT_INCEPTION as 'Initial Profitability',
CG.MEASUREMENT_APPROACH as 'Calculation_Approach',
CG.GROUP_CODE as 'Group ID'

FROM ABC.CONTRACT_GROUP_REFERENCE CGR
LEFT JOIN (SELECT A.REPORTING_PERIOD, A.REPORTING_DATE_PK, REPORTING_PERIOD_END_DATE,A.REPORTING_MONTH,
ROW_NUMBER() OVER ( partition BY A.REPORTING_PERIOD ORDER BY A.REPORTING_MONTH) AS MONTH_SEQ
FROM ABC.REPORTING_DATE a, con.REPORTING_PERIOD b
WHERE a.REPORTING_PERIOD = b.REPORTING_PERIOD
AND a.RECORD_CURRENT_FLAG = 'Y') RP ON RP.REPORTING_DATE_PK = CGR.REPORTING_DATE_FK
LEFT JOIN ABC.CONTRACT C ON C.CONTRACT_PK = CGR.CONTRACT_FK AND C.REPORTING_DATE_FK = RP.REPORTING_DATE_PK
LEFT JOIN ABC.CONTRACT_GROUP CG ON CG.GROUP_PK = CGR.GROUP_FK AND CG.REPORTING_DATE_FK = RP.REPORTING_DATE_PK
LEFT JOIN ABC.INSURANCE_PORTFOLIO IP ON IP.PORTFOLIO_PK = CG.PORTFOLIO_FK

WHERE RP.REPORTING_PERIOD = '2020Q3'
AND RP.MONTH_SEQ = 1
AND IP.PORTFOLIO_CODE = 'CI' );

QUIT;

If that's the case, the connection parameters differ between the two. Different user or different database or something similar.

Also code all the in the same case it harder to read. Try to use the case to gibe sens to your code. I use lower case for language (proc sql; select) and upper case for names (by ABC.CONTRACT_GROUP_REFERENCE) for example.

For the same reason, format the code layout and paste the code here using the appropriate icons.

Re: row_number() OVER ( partition BY

rohitdev_ds — Thu, 12 Nov 2020 18:30:17 GMT

No, its not Oracle, Its in SQL Server

Re: row_number() OVER ( partition BY

ChrisNZ — Fri, 13 Nov 2020 04:02:35 GMT

The reply and comments still apply.