SQL TRANSLATION

[harrylui]

HI ALL,

does anyone know how to translate it in SAS STUDIO(WEB VERSION)

the claimno,pac are both character with the length of 15 -23 

but it seems not able to group by 

this error keeps coming out

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

proc sql;
create table ClaimMedLL as
SELECT NTDIncurredLossExclRevalUSD ,pac,claimno
from Claim
GROUP BY claimno,pac
HAVING Claim.Pac IN ('GMMAM') and SUM(Claim.NTDIncurredLossExclRevalUSD) > 50000
;
run;

[RW9 · Accepted solution]

The reason you get that Note (not an error) is because you are selecting a variable that you are performing statistics on:

SELECT NTDIncurredLossExclRevalUSD

And this:

SUM(Claim.NTDIncurredLossExclRevalUSD) 

Are going against each other, in the latter you are grouping and summing the data, in the first you are selecting all values within that column, no grouping or summing.  You either want one or the other.  I present an example here where the sum is used:

proc sql;
  create table claimmedll as
  select claimno,
         pac,
         sum(ntdincurredlossexclrevalusd) as ntdincurredlossexclrevalusd	
  from   (select * from claim where pac="GMMAM")
  group by claimno,
           pac
  having sum(ntdincurredlossexclrevalusd) > 50000;
run;

Note that I put part of the having in a sub-query, I just find it simpler to see what data feeds into the larger grouping.  As we fix pac as only one where clause, pac could be totally removed from the outer loop without changing the outcome as it will always be GMMAM.

[Reeza]

SAS Studio is SAS 9.4 TS1M5 and the code is the same regardless of which version of SAS you're using, at least for the code shown.