Is there any way to split the below PROC SQL? Client has asked me to do it two steps as follows.
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;
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).
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.
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
;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.