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% |
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;
Does look like a problem to be solved in Excel.
What do you mean by "Add Filter" in SAS?
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:
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.