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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.