topic Re: Splitting the SQL in SAS Programming

Splitting the SQL

Babloo — Tue, 06 Mar 2018 13:46:20 GMT

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;

Re: Splitting the SQL

LinusH — Tue, 06 Mar 2018 14:07:20 GMT

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).

Re: Splitting the SQL

Babloo — Tue, 06 Mar 2018 14:11:55 GMT

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.

Re: Splitting the SQL

LinusH — Tue, 06 Mar 2018 14:47:58 GMT

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.

Re: Splitting the SQL

Tom — Tue, 06 Mar 2018 15:31:19 GMT

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
;