Splitting the SQL

[Babloo]

Is there any way to split the below PROC SQL? Client has asked me to do it two steps as follows.

• first create new amounts (comm_amt, prem_amt, basis_amt)
• than summarize the newly created amounts columns for the group by variables

In the above two steps, I'm not certain on which condition I can do the group by. Appreciate if someone of you guide me here/

PROC SQL;
CREATE TABLE OUTPUT_dijob AS
SELECT DOMAIN,
     INSURANCE_PRODUCT_CD,
     LOB_RK,
     PRODUCER_RK,
     X_PERIL_RK,
     PREMIUM_BEG_DT,
     PREMIUM_END_DT,
     SOURCE_SYSTEM_CD,
     CREATOR_SOURCE_SYSTEM_CD,
     POLICY_NO,
     POLICY_RK,
     INVOICE_NO,
     CURRENCY_CD,
     sum('613113412300'n) as COMM_AMT,
     sum ('7101100000'n ,
         '7101100010'n ,
         '7101101000'n ,
         '710110134010'n ,
          '7101110000'n ,
              '7101111000'n ,
         '7101150020'n ,
         '7101150030'n ,
         '7101300000'n ) as PREM_AMT,
     sum( '6131112300'n,
          '710110000450'n ,
           '7101100010'n ,
               '7101101000'n ,
          '7101101010'n ,
          '7101110000'n ,
               '710111541000'n ,
               '7101150020'n ,
          '7101150030'n ,
               '7101300000'n) as BASIS_AMT
FROM dextc._COMM_POL_PREM_201702
where upcase(domain)='NL' and '21feb2017'd between PREMIUM_BEG_DT and PREMIUM_END_DT
GROUP BY
DOMAIN,
INSURANCE_PRODUCT_CD,
LOB_RK,
PRODUCER_RK,
X_PERIL_RK,
PREMIUM_BEG_DT,
PREMIUM_END_DT,
SOURCE_SYSTEM_CD,
CREATOR_SOURCE_SYSTEM_CD,
POLICY_NO,
POLICY_RK,
INVOICE_NO,
CURRENCY_CD
;
quit;

[LinusH]

Basic guide, just leave out the GROUP BY statement, and the aggregate functions in the first step, and introduce them in the next.

FTR: some of the SUM() in your SQL is not aggregate functions, these are SAS functions that summarize columns vales per row (on those should be kept in the first step).

[Babloo]

Appreciate if you could give me the skeleton of the code. I'm not sure what
aggregate function should I use in the second step as I need to use 'group
by' here.

[LinusH]

The skeleton is your original code.

You should be able to at least use trial and error method to get what you want.

If not, get SQL training - this is basic stuff.

[Tom]

Remember that there is a difference between the SQL aggregate function SUM(), which takes one and only one argument, and the SAS function SUM(,) which takes two or more arguments.

It looks like you need to use both.  To make the pattern consistent I added an extra argument into the first derived variable.  You could just remove the SAS sum(,) function since you already only have only one argument.

CREATE TABLE OUTPUT_dijob AS
SELECT
     DOMAIN
    ,INSURANCE_PRODUCT_CD
    ,LOB_RK
    ,PRODUCER_RK
    ,X_PERIL_RK
    ,PREMIUM_BEG_DT
    ,PREMIUM_END_DT
    ,SOURCE_SYSTEM_CD
    ,CREATOR_SOURCE_SYSTEM_CD
    ,POLICY_NO
    ,POLICY_RK
    ,INVOICE_NO
    ,CURRENCY_CD
    ,sum(sum(.,'613113412300'n)) as COMM_AMT
    ,sum(sum('7101100000'n ,'7101100010'n ,'7101101000'n ,'710110134010'n
        ,'7101110000'n ,'7101111000'n ,'7101150020'n ,'7101150030'n ,'7101300000'n 
        )) as PREM_AMT
    ,sum(sum('6131112300'n ,'710110000450'n ,'7101100010'n ,'7101101000'n 
        ,'7101101010'n ,'7101110000'n ,'710111541000'n ,'7101150020'n 
        ,'7101150030'n ,'7101300000'n
        )) as BASIS_AMT
FROM dextc._COMM_POL_PREM_201702
where upcase(domain)='NL'
  and '21feb2017'd between PREMIUM_BEG_DT and PREMIUM_END_DT
GROUP BY
     DOMAIN
    ,INSURANCE_PRODUCT_CD
    ,LOB_RK
    ,PRODUCER_RK
    ,X_PERIL_RK
    ,PREMIUM_BEG_DT
    ,PREMIUM_END_DT
    ,SOURCE_SYSTEM_CD
    ,CREATOR_SOURCE_SYSTEM_CD
    ,POLICY_NO
    ,POLICY_RK
    ,INVOICE_NO
    ,CURRENCY_CD
;