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)
and the desired table (sample calculation) attached
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.
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?
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.
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
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.