DATA Step, Macro, Functions and more

add new row which will aggregate product subcategory

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

add new row which will aggregate product subcategory

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:

ScenarioRegionCountryProductSubProductPeriodNPEAYLRPYD
ACTUALS/FORECASTAPACAustraliaAutoAuto PCG201650.00%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentA&H Life201600.00%0.00%
ACTUALS/FORECASTAPACAustraliaAutoAuto Non-PCG2016443.72%-15.85%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentAssistance Services201600.00%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentGPA20163.7357.55%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentGrp Travel20168.1678.23%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentInd Travel20169.5739.31%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentIPA20165.5712.33%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentOther2016-0.091204.18%-1088.14%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentPA Disability20162.0959.34%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentSpecialty20163.36105.11%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentSupplemental Health201600.00%0.00%
ACTUALS/FORECASTAPACAustraliaPropertyProperty Non-PCG2016-0.03127.13%-9.39%
ACTUALS/FORECASTAPACAustraliaPropertyProperty PCG201619.8772.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

 

ScenarioRegionCountryProductSubProductPeriodNPEAYLRPYD
ACTUALS/FORECASTAPACAustraliaAutoAuto Non-PCG2016443.72%-15.85%
ACTUALS/FORECASTAPACAustraliaAutoAuto PCG201650.00%0.00%
ACTUALS/FORECASTAPACAustraliaAutoAll2016921.86%-7.92%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentA&H Life201600.00%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentAssistance Services201600.00%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentGPA20163.7357.55%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentGrp Travel20168.1678.23%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentInd Travel20169.5739.31%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentIPA20165.5712.33%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentOther2016-0.091204.18%-1088.14%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentPA Disability20162.0959.34%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentSpecialty20163.36105.11%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal AccidentSupplemental Health201600.00%0.00%
ACTUALS/FORECASTAPACAustraliaPersonal Accidentall201632.39155.60%-108.81%
ACTUALS/FORECASTAPACAustraliaPropertyProperty Non-PCG2016-0.03127.13%-9.39%
ACTUALS/FORECASTAPACAustraliaPropertyProperty PCG201619.8772.12%0.00%
ACTUALS/FORECASTAPACAustraliaPropertyall201619.8499.62%-4.69%

Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 6,939

Re: add new row which will aggregate product subcategory

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super Contributor
Posts: 259

Re: add new row which will aggregate product subcategory

Does look like a problem to be solved in Excel.

 

Contributor
Posts: 46

Re: add new row which will aggregate product subcategory

No i need to write program in sas for this

##- Please type your reply above this line. Simple formatting, no
attachments. -##
PROC Star
Posts: 551

Re: add new row which will aggregate product subcategory

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

Contributor
Posts: 46

Re: add new row which will aggregate product subcategory

Where conditions in sas

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super Contributor
Posts: 259

Re: add new row which will aggregate product subcategory

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
Solution
2 weeks ago
Super User
Posts: 6,939

Re: add new row which will aggregate product subcategory

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 122 views
  • 1 like
  • 4 in conversation