DATA Step, Macro, Functions and more

how to use sas to calculate sum and percentage

Reply
Super Contributor
Posts: 336

how to use sas to calculate sum and percentage

[ Edited ]

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)

Super Contributor
Posts: 336

Re: how to use sas to calculate sum and percentage

[ Edited ]

and the desired table (sample calculation) attached

Super User
Posts: 5,257

Re: how to use sas to calculate sum and percentage

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.

Data never sleeps
Super User
Posts: 10,500

Re: how to use sas to calculate sum and percentage

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?

Super Contributor
Posts: 336

Re: how to use sas to calculate sum and percentage

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.

Super User
Posts: 5,257

Re: how to use sas to calculate sum and percentage

Doing a transpose as I suggested is a good start.
Data never sleeps
Super Contributor
Posts: 336

Re: how to use sas to calculate sum and percentage

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

 

Ask a Question
Discussion stats
  • 6 replies
  • 322 views
  • 0 likes
  • 3 in conversation