turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Splitting the SQL

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-06-2018 08:46 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Babloo

03-06-2018 09:07 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to LinusH

03-06-2018 09:11 AM

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.

aggregate function should I use in the second step as I need to use 'group

by' here.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Babloo

03-06-2018 09:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Babloo

03-06-2018 10:31 AM

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
;
```