https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/244289#M45506 <P>thanks</P><P>the original table that I work on is generated by SAS macro.</P><P>It is hard for me to combine transpose and macro. It would be helpful if you have sample sas code that works with transpose and macro</P><P>&nbsp;</P> Mon, 18 Jan 2016 20:15:31 GMT Bal23 2016-01-18T20:15:31Z https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243841#M45386 <P>I have geneated a table (attached). In stead of calculate it by excel, i hope i can get advice from you to write a code to calculate sum and percentage to generate another table (desired, attached)</P> Wed, 20 Jan 2016 17:23:44 GMT https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243841#M45386 Bal23 2016-01-20T17:23:44Z https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243842#M45387 <P>and the desired table (sample calculation) attached</P> Wed, 20 Jan 2016 17:24:10 GMT https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243842#M45387 Bal23 2016-01-20T17:24:10Z https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243857#M45395 <P>Assuming that you desired is not complete, ther should be percentages throughout the matrix, right?</P> <P>It seems that you have cross tabulation as input, which is not optimal when creating reports.</P> <P>Try to transpose your table to look like this:</P> <P>&nbsp;</P> <P>Id - Category - Year - Value (Freq?)</P> <P>&nbsp;</P> <P>After that, you can use PROC TABULATE to do a cross&nbsp;tabulation with %, sum and more.</P> Fri, 15 Jan 2016 17:57:03 GMT https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243857#M45395 LinusH 2016-01-15T17:57:03Z https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243930#M45415 <P>Why does your "desired" include so many blank cells? If you dont' want to calculate all of the results provide a smaller input example set, the principles for the solution would remain the same and be clearer.</P> <P>&nbsp;</P> <P>Is your sample data actually your input data? I am willing to bet that it is much easier to work from the data you create that sample data from than that result.</P> <P>When I answered a VERY similar question&nbsp;posted yesterday the first thing I had to do to the data was normalize it so that it was amenable to relatively simple summarization using proc tabulate.</P> <P>&nbsp;</P> <P>If your data consisted of:</P> <P>Category</P> <P>Year</P> <P>and a categorical variable that indicated that the record is associated with a Child, Young, Seior or Widow person it is going to be much easier. Are you Counting or summing another variable to the the values displayed?</P> Fri, 15 Jan 2016 21:14:31 GMT https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/243930#M45415 ballardw 2016-01-15T21:14:31Z https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/244259#M45491 <P>sorry I cannot understand this well. How to normalize it?</P><P>&nbsp;</P><P>When I answered a VERY similar question&nbsp;posted yesterday the first thing I had to do to the data was normalize it so that it was amenable to relatively simple summarization using proc tabulate.</P> Mon, 18 Jan 2016 17:26:00 GMT https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/244259#M45491 Bal23 2016-01-18T17:26:00Z https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/244262#M45493 Doing a transpose as I suggested is a good start. Mon, 18 Jan 2016 18:12:32 GMT https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/244262#M45493 LinusH 2016-01-18T18:12:32Z https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/244289#M45506 <P>thanks</P><P>the original table that I work on is generated by SAS macro.</P><P>It is hard for me to combine transpose and macro. It would be helpful if you have sample sas code that works with transpose and macro</P><P>&nbsp;</P> Mon, 18 Jan 2016 20:15:31 GMT https://communities.sas.com/t5/SAS-Programming/how-to-use-sas-to-calculate-sum-and-percentage/m-p/244289#M45506 Bal23 2016-01-18T20:15:31Z