BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
subrat1
Fluorite | Level 6

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%
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

6 REPLIES 6
andreas_lds
Jade | Level 19

Does look like a problem to be solved in Excel.

 

subrat1
Fluorite | Level 6
No i need to write program in sas for this

##- Please type your reply above this line. Simple formatting, no
attachments. -##
PeterClemmensen
Tourmaline | Level 20

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

subrat1
Fluorite | Level 6
Where conditions in sas

##- Please type your reply above this line. Simple formatting, no
attachments. -##
andreas_lds
Jade | Level 19

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
Super User

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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