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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.