add new row which will aggregate product subcategory

[subrat1]

I have input data in excel , i have to apply filter on product column and add a new row which will calculate other column.

My input data is:

Scenario	Region	Country	Product	SubProduct	Period	NPE	AYLR	PYD
ACTUALS/FORECAST	APAC	Australia	Auto	Auto PCG	2016	5	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	A&H Life	2016	0	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Auto	Auto Non-PCG	2016	4	43.72%	-15.85%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Assistance Services	2016	0	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	GPA	2016	3.73	57.55%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Grp Travel	2016	8.16	78.23%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Ind Travel	2016	9.57	39.31%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	IPA	2016	5.57	12.33%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Other	2016	-0.09	1204.18%	-1088.14%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	PA Disability	2016	2.09	59.34%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Specialty	2016	3.36	105.11%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Supplemental Health	2016	0	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Property	Property Non-PCG	2016	-0.03	127.13%	-9.39%
ACTUALS/FORECAST	APAC	Australia	Property	Property PCG	2016	19.87	72.12%	0.00%

I want my output to be: so it add filter on product (auto)  , add new row and calculate sum of AYLR and average of PYD

Scenario	Region	Country	Product	SubProduct	Period	NPE	AYLR	PYD
ACTUALS/FORECAST	APAC	Australia	Auto	Auto Non-PCG	2016	4	43.72%	-15.85%
ACTUALS/FORECAST	APAC	Australia	Auto	Auto PCG	2016	5	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Auto	All	2016	9	21.86%	-7.92%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	A&H Life	2016	0	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Assistance Services	2016	0	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	GPA	2016	3.73	57.55%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Grp Travel	2016	8.16	78.23%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Ind Travel	2016	9.57	39.31%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	IPA	2016	5.57	12.33%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Other	2016	-0.09	1204.18%	-1088.14%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	PA Disability	2016	2.09	59.34%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Specialty	2016	3.36	105.11%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	Supplemental Health	2016	0	0.00%	0.00%
ACTUALS/FORECAST	APAC	Australia	Personal Accident	all	2016	32.39	155.60%	-108.81%
ACTUALS/FORECAST	APAC	Australia	Property	Property Non-PCG	2016	-0.03	127.13%	-9.39%
ACTUALS/FORECAST	APAC	Australia	Property	Property PCG	2016	19.87	72.12%	0.00%
ACTUALS/FORECAST	APAC	Australia	Property	all	2016	19.84	99.62%	-4.69%

[andreas_lds]

Does look like a problem to be solved in Excel.

[subrat1]

No i need to write program in sas for this

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[PeterClemmensen]

What do you mean by "Add Filter" in SAS?

[subrat1]

Where conditions in sas

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[andreas_lds]

No, you don't have to do write a program. Maybe you want to, or somebody else forces you to do so.

The following steps will be necessary:

1. import the excel file
2. verify type and length of all variables: it is not very likely that proc import guesses everything in the required form
3. fix what has not been guessed correctly,
4. apply the filter (can be added to the calculating proc)
5. do the calculation: proc report seems to be able to do it
6. export the result to excel

[Kurt_Bremser · Accepted solution]

Sort by product, then do this:

data want;
set have;
by product;
if first.product
then do;
  sum_npe = 0;
  sum_aylr = 0;
  sum_pyd = 0;
  counter = 0;
end;
sum_npe + npe;
sum_aylr + aylr;
sum_pyd + pyd;
counter + 1;
output;
if last.product
then do;
  npe = sum_npe;
  aylr = sum_aylr / counter;
  pyd = sum_pyd / counter;
  output;
end;
drop sum_npe sum_aylr sum_pyd counter;
run;