topic Re: add new row which will aggregate product subcategory in SAS Programming https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379682#M91402 <P>Sort by product, then do this:</P> <PRE><CODE class=" language-sas">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;</CODE></PRE> Thu, 27 Jul 2017 11:57:00 GMT Kurt_Bremser 2017-07-27T11:57:00Z add new row which will aggregate product subcategory https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379670#M91394 <P>I have input data in excel , i have to apply filter on product column and add a new row which will calculate other column.</P><P>My input data is:</P><TABLE><TBODY><TR><TD>Scenario</TD><TD>Region</TD><TD>Country</TD><TD>Product</TD><TD>SubProduct</TD><TD>Period</TD><TD>NPE</TD><TD>AYLR</TD><TD>PYD</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Auto</TD><TD>Auto PCG</TD><TD>2016</TD><TD>5</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>A&amp;H Life</TD><TD>2016</TD><TD>0</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Auto</TD><TD>Auto Non-PCG</TD><TD>2016</TD><TD>4</TD><TD>43.72%</TD><TD>-15.85%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Assistance Services</TD><TD>2016</TD><TD>0</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>GPA</TD><TD>2016</TD><TD>3.73</TD><TD>57.55%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Grp Travel</TD><TD>2016</TD><TD>8.16</TD><TD>78.23%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Ind Travel</TD><TD>2016</TD><TD>9.57</TD><TD>39.31%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>IPA</TD><TD>2016</TD><TD>5.57</TD><TD>12.33%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Other</TD><TD>2016</TD><TD>-0.09</TD><TD>1204.18%</TD><TD>-1088.14%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>PA Disability</TD><TD>2016</TD><TD>2.09</TD><TD>59.34%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Specialty</TD><TD>2016</TD><TD>3.36</TD><TD>105.11%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Supplemental Health</TD><TD>2016</TD><TD>0</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Property</TD><TD>Property Non-PCG</TD><TD>2016</TD><TD>-0.03</TD><TD>127.13%</TD><TD>-9.39%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Property</TD><TD>Property PCG</TD><TD>2016</TD><TD>19.87</TD><TD>72.12%</TD><TD><P>0.00%</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I want my output to be: so it add filter on product (auto)&nbsp; , add new row and calculate sum of AYLR and average of PYD</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Scenario</TD><TD>Region</TD><TD>Country</TD><TD>Product</TD><TD>SubProduct</TD><TD>Period</TD><TD>NPE</TD><TD>AYLR</TD><TD>PYD</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Auto</TD><TD>Auto Non-PCG</TD><TD>2016</TD><TD>4</TD><TD>43.72%</TD><TD>-15.85%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Auto</TD><TD>Auto PCG</TD><TD>2016</TD><TD>5</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Auto</TD><TD>All</TD><TD>2016</TD><TD>9</TD><TD>21.86%</TD><TD>-7.92%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>A&amp;H Life</TD><TD>2016</TD><TD>0</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Assistance Services</TD><TD>2016</TD><TD>0</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>GPA</TD><TD>2016</TD><TD>3.73</TD><TD>57.55%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Grp Travel</TD><TD>2016</TD><TD>8.16</TD><TD>78.23%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Ind Travel</TD><TD>2016</TD><TD>9.57</TD><TD>39.31%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>IPA</TD><TD>2016</TD><TD>5.57</TD><TD>12.33%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Other</TD><TD>2016</TD><TD>-0.09</TD><TD>1204.18%</TD><TD>-1088.14%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>PA Disability</TD><TD>2016</TD><TD>2.09</TD><TD>59.34%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Specialty</TD><TD>2016</TD><TD>3.36</TD><TD>105.11%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>Supplemental Health</TD><TD>2016</TD><TD>0</TD><TD>0.00%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Personal Accident</TD><TD>all</TD><TD>2016</TD><TD>32.39</TD><TD>155.60%</TD><TD>-108.81%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Property</TD><TD>Property Non-PCG</TD><TD>2016</TD><TD>-0.03</TD><TD>127.13%</TD><TD>-9.39%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Property</TD><TD>Property PCG</TD><TD>2016</TD><TD>19.87</TD><TD>72.12%</TD><TD>0.00%</TD></TR><TR><TD>ACTUALS/FORECAST</TD><TD>APAC</TD><TD>Australia</TD><TD>Property</TD><TD>all</TD><TD>2016</TD><TD>19.84</TD><TD>99.62%</TD><TD>-4.69%</TD></TR></TBODY></TABLE> Thu, 27 Jul 2017 11:07:47 GMT https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379670#M91394 subrat1 2017-07-27T11:07:47Z Re: add new row which will aggregate product subcategory https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379673#M91397 <P>Does look like a problem to be solved in Excel.</P> <P>&nbsp;</P> Thu, 27 Jul 2017 11:33:56 GMT https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379673#M91397 andreas_lds 2017-07-27T11:33:56Z Re: add new row which will aggregate product subcategory https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379674#M91398 No i need to write program in sas for this<BR /><BR />##- Please type your reply above this line. Simple formatting, no<BR />attachments. -## Thu, 27 Jul 2017 11:36:39 GMT https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379674#M91398 subrat1 2017-07-27T11:36:39Z Re: add new row which will aggregate product subcategory https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379675#M91399 <P>What do you mean by "Add Filter" in SAS?</P> Thu, 27 Jul 2017 11:38:45 GMT https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379675#M91399 PeterClemmensen 2017-07-27T11:38:45Z Re: add new row which will aggregate product subcategory https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379676#M91400 Where conditions in sas<BR /><BR />##- Please type your reply above this line. Simple formatting, no<BR />attachments. -## Thu, 27 Jul 2017 11:41:39 GMT https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379676#M91400 subrat1 2017-07-27T11:41:39Z Re: add new row which will aggregate product subcategory https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379680#M91401 <P>No, you don't have to do write a program. Maybe you want to, or somebody else forces you to do so.</P> <P>&nbsp;</P> <P>The following steps will be necessary:</P> <OL> <LI>import the excel file</LI> <LI>verify type and length of all variables: it is not very likely that proc import guesses everything in the required form</LI> <LI>fix what has not been guessed correctly,</LI> <LI>apply the filter (can be added to the calculating proc)</LI> <LI>do the calculation: proc report seems to be able to do it</LI> <LI>export the result to excel</LI> </OL> Thu, 27 Jul 2017 11:53:17 GMT https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379680#M91401 andreas_lds 2017-07-27T11:53:17Z Re: add new row which will aggregate product subcategory https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379682#M91402 <P>Sort by product, then do this:</P> <PRE><CODE class=" language-sas">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;</CODE></PRE> Thu, 27 Jul 2017 11:57:00 GMT https://communities.sas.com/t5/SAS-Programming/add-new-row-which-will-aggregate-product-subcategory/m-p/379682#M91402 Kurt_Bremser 2017-07-27T11:57:00Z