SQL grouping problem

[harrylui]

Dear all,

this is my data set 

Product	UWYear	AccidentYearMonth	MasterPolicy	PolicyHolder	ProducerName	ClaimNo	Coverage	LossIncurred
ABC	2018	201809	ACEGT00787	park	lalala	4045563205	01ZG	-2,547,181
ABC	2018	201809	ACEGT00787	park	lalala	4045563205	01ZG	60
ABC	2018	201809	ACEGT00787	park	lalala	4045563205	01ZG	2,547,181
ABC	2018	201809	ACEGT00787	park	lalala	4045563205	01ZG	-60
ABC	2018	201810	ACEGT00787	park	lalala	4045563205	01ZG	5,347
ABC	2018	201810	ACEGT00787	park	lalala	4045563205	01ZG	-5,347
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	-1,041
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	1,041
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	1,040
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	-729
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	729
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	728
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	729
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	-729
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	-728

I want to group it using ClaimNo and lossincurred as a main grouping 

the expected output list below 

Product	UWYear	AccidentYearMonth	MasterPolicy	PolicyHolder	ProducerName	ClaimNo	Coverage	LossIncurred
ABC	2018	201810	ACEGT00787	park	lalala	4045563205	01ZG	0
Other	2016	201603	ACEGT00739	asia	lelele	5180026121	55ZG	1040

can anyone help me with this

if possible,please tell me how to transform -2,547,181  into negative numeric number in sas

Thank you all

[Kurt_Bremser · Accepted solution]

SQL can do it simply:

proc sql;
create table want as
select
  Product,UWYear,AccidentYearMonth,MasterPolicy,
  PolicyHolder,ProducerName,ClaimNo,Coverage,
  sum(lossincurred) as lossincurred
from have
group by
  Product,UWYear,AccidentYearMonth,MasterPolicy,
  PolicyHolder,ProducerName,ClaimNo,Coverage
;
quit;

Note that -2,547,181 is already a valid numeric value, if the column type is numeric. Otherwise, convert by using the comma. informat:

data test;
charvar = "-2,547,181";
numvar = input(charvar,comma12.);
run;

[harrylui]

thank you