Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.
Some generic comments:
Waste of clock cycles:
data Aaa; set Aa;
run;
The AAA set is used ONLY in
data A1; set Aaa;
output;
female = 99 ; * everyone;
output;
Might as well use AA on the set statement.
Multiple uses of creating additional unneeded data sets can lead to excessive run times as each record gets processed multiple times instead of once. In some environments you will be limited in disk space by an Admin setting. Sets like this count against your limit while serving no purpose.
Adding an additional level to a variable just to create an "all records" is also a suspect activity in general. Proc Tabulate does a very simple job with the ALL instruction for creating summaries for all values of a categorical variable:
You should have the SASHELP.CLASS data set to run this code as an example.
proc tabulate data =sashelp.class;
class sex age;
table sex all='All sexes',
age all='All ages'
;
run;
Proc Report does summaries in columns using the Break instruction.
Then your final output with multiple values in a cell like your "X.xx (x.xx, x.xx) " are going to be a tad ugly in terms of aligned values unless every single value in each cell of the column has values of very similar ranges.
Consider your process as a whole for what will need to be done when you need to add Race as a group next year and need to have the gender as you have it now, a race only summary and gender and race.
Then how would you modify that to incorporate Hispanic Ethnicity after that, or a geography such as State, or county (or better yet both)? You are going to create so many data sets that you will get lost keeping track of them, or which one holds what.
One project I worked on with school data required summaries at just about every combination of : Management region, county, school district, school name, grade, school type (public private), summarizing numbers and percentages of students vaccinated for seven diseases.
Project used 4 data sets to create 2,000+ pages of report. 1 set was the raw data read by the provider, 1 was that "cleaned up" after identifying and getting corrected totals for things like enrollments and immunization incomplete values and misspelled schools and such. Third was a summary data set used to get the counts of all the combinations needed (Prod summary is your friend). The fourth set used the counts to create percentages of enrolled students. Then specific records were displayed in the report.
The third year I was doing this report I had to add another analysis group level (a third level for school type).
My code did not require any changes to get report tables separated or summarized for that new type. Schools were changed every year. New school no code change. School removed, no code changed. School districts combined into a single district, no code change (for a single year; cross year reports would require making sure the identifications are the same for each reported year). When two immunizations were added the changes were 1) in the read program to get the new values, added to the summarization step (add names to a Var statement), and in the 4th step created the new calculated variables. Since these were done in ARRAYS of like used variables that meant adding 3 additional variables to the array definitions and then providing new LABELS for those variables.
