BookmarkSubscribeRSS Feed
Bal23
Lapis Lazuli | Level 10

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)

6 REPLIES 6
Bal23
Lapis Lazuli | Level 10

and the desired table (sample calculation) attached

LinusH
Tourmaline | Level 20

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
ballardw
Super User

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
Lapis Lazuli | Level 10

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
Tourmaline | Level 20
Doing a transpose as I suggested is a good start.
Data never sleeps
Bal23
Lapis Lazuli | Level 10

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 989 views
  • 0 likes
  • 3 in conversation