how to use sas to calculate sum and percentage

[Bal23]

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)

[Bal23]

and the desired table (sample calculation) attached

[LinusH]

Assuming that you desired is not complete, ther should be percentages throughout the matrix, right?

It seems that you have cross tabulation as input, which is not optimal when creating reports.

Try to transpose your table to look like this:

Id - Category - Year - Value (Freq?)

After that, you can use PROC TABULATE to do a cross tabulation with %, sum and more.

[ballardw]

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.

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.

When I answered a VERY similar question 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.

If your data consisted of:

Category

Year

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?

[Bal23]

sorry I cannot understand this well. How to normalize it?

When I answered a VERY similar question 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.

[LinusH]

Doing a transpose as I suggested is a good start.

[Bal23]

thanks

the original table that I work on is generated by SAS macro.

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