Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- Web Report Studio
- /
- SQL Translate to SAS

Options

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 11-02-2018 12:18 AM
(1446 views)

Can anyone help with my issue?

i am stuck in this problem for few days

the Note listed below is keep coming out:

NOTE: The query requires remerging summary statistics back with the original data.

this is my code:

PROC SQL;

CREATE TABLE

WrittenData as

SELECT

CASE WHEN Product IN ('371004', '371047','376003','374008') THEN 'PC' ELSE 'AH' END as BusinessUnit

,Period

,UWYear

,ExposurePeriod

,SUM(GWP) as GWP

,SUM(NWP) as NWP

,SUM(Commission) as Commission

,SUM(GWP_USD) as GWP_USD

,SUM(NWP_USD) as NWP_USD

,SUM(Commission_USD) as Commission_USD

FROM Written

WHERE

Written.Period >= 201201

AND

Written.Pac IN ('GMMAM')

GROUP BY CASE WHEN Product IN ('371004', '371047', '376003', '374008') THEN 'PC' ELSE 'AH' END, Period, UWYear, ExposurePeriod

;

QUIT;

and this is my log

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

61

62

63

64 PROC SQL;

65 CREATE TABLE

66 WrittenData as

67 SELECT

68 CASE WHEN Product IN ('371004', '371047','376003','374008') THEN 'PC' ELSE 'AH' END as BusinessUnit

69 ,Period

70 ,UWYear

71 ,ExposurePeriod

72 ,SUM(GWP) as GWP

73 ,SUM(NWP) as NWP

74 ,SUM(Commission) as Commission

75 ,SUM(GWP_USD) as GWP_USD

76 ,SUM(NWP_USD) as NWP_USD

77 ,SUM(Commission_USD) as Commission_USD

78 FROM Written

79 WHERE

80 Written.Period >= 201201

81 AND

82 Written.Pac IN ('GMMAM')

83 GROUP BY CASE WHEN Product IN ('371004', '371047', '376003', '374008') THEN 'PC' ELSE 'AH' END, Period, UWYear,

83 ! ExposurePeriod

84 ;

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: Table WORK.WRITTENDATA created, with 821 rows and 10 columns.

85 QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

86

87

88

89 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

102

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You've run into a shortcoming of the SAS SQL compiler that I have adressed in https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhance-the-quot-group-by-quot-evaluation-logic-...

Use the calculated variable in the group by:

```
proc sql;
create table writtenData as
select
case
when Product in ('371004','371047','376003','374008') then 'PC'
else 'AH'
end as BusinessUnit,
Period,
UWYear,
ExposurePeriod,
sum(GWP) as GWP,
sum(NWP) as NWP,
sum(Commission) as Commission,
sum(GWP_USD) as GWP_USD,
sum(NWP_USD) as NWP_USD,
sum(Commission_USD) as Commission_USD
from Written
where
Written.Period >= 201201
and
Written.Pac in ('GMMAM')
group by
calculated BusinessUnit,
Period,
UWYear,
ExposurePeriod
;
quit;
```

Compare the look of my code with your code as posted; proper visual formatting goes a long way in understanding what's going on:

@harrylui wrote:

Can anyone help with my issue?

i am stuck in this problem for few days

the Note listed below is keep coming out:

NOTE: The query requires remerging summary statistics back with the original data.

this is my code:

PROC SQL;

CREATE TABLE

WrittenData as

SELECT

CASE WHEN Product IN ('371004', '371047','376003','374008') THEN 'PC' ELSE 'AH' END as BusinessUnit

,Period

,UWYear

,ExposurePeriod

,SUM(GWP) as GWP

,SUM(NWP) as NWP

,SUM(Commission) as Commission

,SUM(GWP_USD) as GWP_USD

,SUM(NWP_USD) as NWP_USD

,SUM(Commission_USD) as Commission_USD

FROM Written

WHERE

Written.Period >= 201201

AND

Written.Pac IN ('GMMAM')

GROUP BY CASE WHEN Product IN ('371004', '371047', '376003', '374008') THEN 'PC' ELSE 'AH' END, Period, UWYear, ExposurePeriod

;

QUIT;

and this is my log

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;61626364 PROC SQL;65 CREATE TABLE66 WrittenData as67 SELECT68 CASE WHEN Product IN ('371004', '371047','376003','374008') THEN 'PC' ELSE 'AH' END as BusinessUnit69 ,Period70 ,UWYear71 ,ExposurePeriod72 ,SUM(GWP) as GWP73 ,SUM(NWP) as NWP74 ,SUM(Commission) as Commission75 ,SUM(GWP_USD) as GWP_USD76 ,SUM(NWP_USD) as NWP_USD77 ,SUM(Commission_USD) as Commission_USD78 FROM Written79 WHERE80 Written.Period >= 20120181 AND82 Written.Pac IN ('GMMAM')83 GROUP BY CASE WHEN Product IN ('371004', '371047', '376003', '374008') THEN 'PC' ELSE 'AH' END, Period, UWYear,83 ! ExposurePeriod84 ;NOTE: The query requires remerging summary statistics back with the original data.NOTE: Table WORK.WRITTENDATA created, with 821 rows and 10 columns.85 QUIT;NOTE: PROCEDURE SQL used (Total process time):real time 0.01 secondscpu time 0.01 seconds86878889 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;102

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Lets start by making that code readable, by adding formatting, using the code window (its the {i} above post area), and by not shouting the code:

proc sql; create table writtendata as select businessunit ,period ,uwyear ,exposureperiod ,sum(gwp) as gwp ,sum(nwp) as nwp ,sum(commission) as commission ,sum(gwp_usd) as gwp_usd ,sum(nwp_usd) as nwp_usd ,sum(commission_usd) as commission_usd from (select *, case when product in ('371004', '371047','376003','374008') then 'PC'

else 'AH' end as businessunit from written where period >= 201201 and pac='GMMAM') group by businessunit, period, uwyear, exposureperiod; quit;

You will see that I do the filtering in a sub-query.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

thank you! I solved it!

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.