topic Re: SQL Translate to SAS in SAS Web Report Studio

SQL Translate to SAS

harrylui — Fri, 02 Nov 2018 04:18:53 GMT

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;

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

89 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

102

Re: SQL Translate to SAS

Kurt_Bremser — Fri, 02 Nov 2018 08:54:44 GMT

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-of-the-SQL/idi-p/491111

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

Re: SQL Translate to SAS

RW9 — Fri, 02 Nov 2018 09:01:11 GMT

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.

Re: SQL Translate to SAS

harrylui — Mon, 05 Nov 2018 04:18:16 GMT

thank you! I solved it!