SQL grouping problem

harrylui — Tue, 11 Dec 2018 08:06:30 GMT

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

Re: SQL grouping problem

Kurt_Bremser — Tue, 11 Dec 2018 08:18:33 GMT

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;

Re: SQL grouping problem

harrylui — Tue, 18 Dec 2018 08:39:35 GMT

thank you

topic Re: SQL grouping problem in New SAS User

SQL grouping problem

Re: SQL grouping problem

Re: SQL grouping problem