Hi:
There are many examples of using COMPUTE blocks with ACROSS variables and PROC REPORT -- in the documentation and in previous forum postings.
The key is using ABSOLUTE column numbers inside the COMPUTE block. For example, as soon as you have more than one value for ORIGIN, what does the compute block do with your computation???? When you take the average and use the COLUMN name -- do you want to take the average for the first value for ORIGIN or the second value for ORIGIN. You know what you want -- you want the average for EACH unique value for ORIGIN.
But PROC REPORT requires you to use the ABSOLUTE column number in the computation.
I had a different example that used SASHELP.PRDSALE. If you look at the following code, it produces the average of ACTUAL and PREDICT sales amounts for each REGION (there are 2 values for REGION):
I have altered the LISTING output slightly to show the ABSOLUTE column numbers that PROC REPORT would assign to the ACROSS variables:
[pre]
+----------------------------------------------------------------------------------------------------------------------+
| |---------------------------------------------------Region--------------------------------------------------|
| |-------------------------EAST------------------------|-------------------------WEST------------------------|
|Product | Actual Sales| Predicted Sales| compavg| Actual Sales| Predicted Sales| compavg|
|----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------|
|BED | $5,708.00| $6,780.00| $6,244.00| $7,251.00| $6,699.00| $6,975.00|
|----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------|
|CHAIR | $6,831.00| $5,733.00| $6,282.00| $5,640.00| $4,917.00| $5,278.50|
|----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------|
| | _c2_ | _c3_ | _c4_ | _c5_ | _c6_ | _c7_ |
+----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+
^
|-Conceptually PRODUCT
is _C1_
[/pre]
Since there are 3 items under each value for region, then _c2_, _c3_ and _c4_ are the values for ACTUAL, PREDICT and COMPAVG for REGION=EAST and _c5_, _c6_ and _c7_ are the values for ACTUAL, PREDICT and COMPAVG for REGION=WEST. If there were more regions, you would have more "sets" of 3 items for each unique value for REGION.
To help you get used to the idea of ABSOLUTE column numbers, the OUT= option used with PROC REPORT gives you a way to see EXACTLY which absolute column numbers are assigned by PROC REPORT for a particular set of data. In the code below, the OUT= dataset is displayed with PROC PRINT -- so you can see the ABSOLUTE column numbers that PROC REPORT assigned internally to each ACROSS item -- plus it added the _BREAK_ variable to the output (in this case, there are no summary lines that are created by PROC REPORT, so _BREAK_ will be blank for all rows).
cynthia
[pre]
proc sort data=sashelp.prdsale out=prdsale;
where quarter = 1 and year = 1994 and
country in ("CANADA", "GERMANY") and
product in ("BED", "CHAIR");
by product region;
run;
ods listing close;
ods html file='c:\temp\across_compute.html' style=sasweb;
proc report data=prdsale nowd out=showcols;
column product region,(actual predict compavg);
define product/group;
define region/ across '-Region-';
define actual / sum f=dollar16.2;
define predict / sum f=dollar16.2;
define compavg / computed f=dollar16.2;
compute compavg;
_c4_ = mean(_c2_, _c3_);
_c7_ = mean(_c5_, _c6_);
endcomp;
run;
proc print data=showcols noobs;
title 'Absolute Column Numbers';
run;
ods html close;
[/pre]