Proc Report: Attempting to add column mean... but can't seem to get it to work.

[agille05]

Hi everyone,

Hoping someone might be able to give me a hand. I'm newer to SAS and am using Proc Report for the first time. I have the following code: 

%Macro PerCapitaReport(Dx);
Proc Report Data=Mitacs.Merge Missing;
   Where &Dx=1;
   Title "&Dx";
   Column Province &Dx, (n sum pctn) Population PerCapita Age, Mean;
   Define Province / group style (column) = Header;
   Define &Dx / sum;
   Define n / 'Count' f=comma6.;
   Define sum / 'Sum';
   Define pctn / 'Percent' f=percent9.2;
   Define population / 'Population' group style (column) = Header f=comma12.;
   Define percapita / computed 'Rate Per/10,000 Population' f=12.2;
   Define age / 'Mean Age' f=9.;
   Rbreak after / summarize style(summary) = Header;
   Compute percapita; percapita=(&Dx..sum / population)*10000;
   Endcomp;
   Compute After; Province = 'Total/Mean';
   Endcomp;
Run;
%Mend PerCapitaReport;

%PerCapitaReport(Arthritis)

which generates the following table: 

However, as you can see, I can't seem to get a grand mean total for the 'Rate per 10,000 population' column. I've read through a number of guides and tried a bunch of things, to no avail.

Appreciate any assistance anyone can offer 🙂

[ballardw]

I really hope you do not expect the total row mean of the Rate Per/10,000 Population to be the mean of the displayed values in the column. That would only be reasonable when the denominators of the rates (i.e. the population) are identical. You may have a similar issue with your Mean Age.

You should provide some example data that we can test your code against in the form of a data step. Do not include any sensitive variables and provide just enough Province values to exercise the code, maybe 3 or 4.

Your compute after for a combined rate is going to need to use a total numerator and denominator and recalculate a value with that information in the Compute after. With out data I am not going to guess what that needs to actually be for your data.

When an N and a Sum are the same I really need to see some data to see what is going on.

You might be better off using a procedure like Proc Summary to summarize the data counts and calculate the rates before Proc Report as this can get to be an obnoxious exercise to debug.

If you use Proc Summary with Province as a Class variable you well get on value, _type_= 0 that has the sums, counts or what have you overall. So a small data step to calculate the rates would include a total rate, assign a value for Provinde, it would be missing by default. Population might be Weight Variable for calculations. May be easier to then use Proc Print instead of Report.

One minor note, if you are currently using a Format of $2. for Province you may want to provide a Format in the Report for Province of $10. so the Total/Mean text does not get truncated to 2 characters.

[agille05]

Hi Ballardw,

Thank you ever so much for your response. I had considered the denominator and ran a proc means to confirm it is using the correct denominator for age (which is was), but will be sure to do the same for the rate. 

You've given me some great suggestions. Will try Proc Summary instead. 

Thank you ever so much for your time. 

[ballardw]

Summary is Means with the default behavior of creating output data, so you shouldn't be too surprised.

Suggestion since you are using a macro variable to represent your report variable. Put all of the analysis variables in one call to Proc Summary and use the / autoname option on the output statement so you get the n, sum etc in a single pass.

Then your report code could use &dx.: to pull the needed variables into a procedure.

A data step with an array of the values needed may be the easiest way to get many rates calculated quickly.

I have done this with up to 50 VAR variables and 15 Class variables to get the summaries I wanted. Once the data exists it is often fairly easy to select the needed variables/ records for a report. All said and done my project with this stuff generated roughly 2,000 pages of output to be complete and in a form people could access the tables.