Hi:
I thought it possible that PCTN or PCTSUM would not suit your need. But I don't think calculating EARNING_PER in a DATA step is going to work either. I really do think the COMPUTE block is the way to go. And, you're right...the fact is that it -is- tedious to hard-code computed items, since you have to use absolute column numbers when you are combining COMPUTED items with ACROSS items.
But, since the computed items fall into a predictable pattern, it is easy to write a macro program to generate the COMPUTE block logic for your program, as shown in this paper on pages 12, 13 and 14:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf
For example, given the data and variables that you showed in your original post, your COMPUTE block would look something like this:
[pre]
PROC REPORT DATA = test nowd;
COLUMN STATE CITY AREA,(PEOPLE EARNING EARNING_PER);
DEFINE STATE/GROUP;
DEFINE CITY/GROUP;
DEFINE AREA/ACROSS;
DEFINE PEOPLE/sum;
DEFINE EARNING/sum;
DEFINE EARNING_PER/computed f=PERCENT10.2 'Earning Per';
BREAK AFTER STATE/SUMMARIZE;
RBREAK AFTER/SUMMARIZE;
compute earning_per;
** Use Absolute column numbers in COMPUTE block for ACROSS items;
_c5_ = _c4_ / _c3_;
_c8_ = _c7_ / _c6_;
_c11_ = _c10_ / _c9_;
endcomp;
RUN;
[/pre]
You can see the pattern that the items fall into. Conceptually, State and City are the first and second column on the report. That means that for Area A, column 3 is PEOPLE, column 4 is EARNING and column 5 is the calculated EARNING_PER. Then, Area B starts... Proc REPORT assigns a special number variables which are under ACROSS items. The column numbers are: _C??_ where the ?? would be replaced by the variable's position on the report row, as calculated from the number of ACROSS items and the number of variables under each ACROSS item. For your data, the pattern is:
_C3_, _C6_ and _C9_ represent the PEOPLE value for areas A, B and C
_C4_, _C7_ and _C10_ represent the EARNING value for areas A, B and C
_C5_, _c8_ and _C11_ represent the computed earning_per for areas A, B and C
As it explains in the paper, once you know how many items will be -under- each ACROSS item (in your case, 3 items under each across item) and you know how many items are on the report row -before- the ACROSS items start (in your case, there are 2 items BEFORE Area A starts so the first absolute column number is _c3_), you can then write a macro program that will generate as many statements in the COMPUTE block as you need. You don't even have to know how many ACROSS items there are -- you can have the macro program do that for you.
Otherwise, your choice would be to presummarize all the data using a different procedure and then calculate the percent manually-- but on the summarized data.
cynthia
Click image to register for webinar
