BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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;
4 REPLIES 4
LinusH
Tourmaline | Level 20

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

Data never sleeps
Babloo
Rhodochrosite | Level 12
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
Tourmaline | Level 20

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.

Data never sleeps
Tom
Super User Tom
Super User

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
;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1336 views
  • 0 likes
  • 3 in conversation