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