Hi:
If you had a simple column statement with NO across usage variables like this, then the compute block would be simple:
[pre]
column region subsidiary product sales returns calc_col;
... more code ...
compute calc_col;
calc_col = divide(sales.sum,returns.sum);
endcomp;
[/pre]
In the above scenario, there is no ambiguity about how sales and returns are being used to create the new report item, CALC_COL. All you have to do is use the expected compound name (assuming that SALES and RETURNS both have a usage of SUM and CALC_COL has a usage of COMPUTED.
You have 5 columns from the data (SASHELP.SHOES) on the report and 1 computed column. Without any across variables, every report row would only need 1 execution of the compute block for CALC_COL.
However, as soon as you define a variable to have the ACROSS usage, then it is possible that your COMPUTE block would execute more than 1 time on each report row. Therefore, this form of assignment statement
[pre]
calc_col = divide(sales.sum,returns.sum);
[/pre]
will no longer work. That's because you will have a CALC_COL for the first ACROSS group, another CALC_COL for the second ACROSS group, and the third, and the fourth, etc, etc. So, in order to write the correct assignment statement, you need to use absolute column numbers for any of the ACROSS items involved in the compute block. For example:
[pre]
column region subsidiary product,(sales returns calc_col);
[/pre]
Now, let's say you had 3 groups of product values. REGION is conceptually the first column on the report; subsidiary is the second column on the report. SALES for the first PRODUCT value is _C3_ as an absolute name. RETURNS for the first PRODUCT value is _C4_ and CALC_COL for the first product value is _C5_. So using these absolute names, the assignment statement would be:
[pre]
... more code ...
compute calc_col;
_c5_ = divide(_c3_,_c4_);
endcomp;
[/pre]
If your PRODUCT had 3 unique values, then your complete COMPUTE block would be:
[pre]
... more code ...
compute calc_col;
_c5_ = divide(_c3_ , _c4_);
_c8_ = divide(_c6_ , _c7_);
_c11_ = divide(_c9_ , _c10_);
endcomp;
[/pre]
Proc REPORT goes through a setup phase when it starts and one of the things it does is assign absolute column numbers to across items during the setup phase. You can detect the absolute column numbers it's going to assign by using OUT= with PROC REPORT to create an output dataset (which for across items, will contain the absolute column names that PROC REPORT assigned):
[pre]
ods html file='c:\temp\shoes_abs.html' style=sasweb;
proc report data=sashelp.shoes nowd out=work.abscol;
title 'ACROSS Example';
where region in ("Asia", "Canada") and
product in ("Sandal", "Boot", "Slipper");
column region subsidiary
product,(sales returns calc_col);
define region / group 'Region';
define subsidiary / group 'Subsidiary';
define product / across 'Product';
define sales / sum format=comma12.2 'Sales';
define returns / sum format=comma12.2 'Returns';
define calc_col / computed format=8.3 ;
compute calc_col;
calc_col = .;
endcomp;
run;
proc print data=work.abscol;
run;
ods html close;
[/pre]
A close examination of WORK.ABSCOL will reveal how the column names work. since I have 3 unique PRODUCT values and 3 items nested under each unique PRODUCT value, it is easy to figure out what the column names will be if I had 4 or 5 unique PRODUCT values. (In fact, if you're comfortable with SAS Macro programming, you can write a macro program to generate the compute block assignment statements for you.) Note that in this output. I set the value of CALC_COL to missing on purpose, because the focus of the above program is to reveal what column names need to be used in the COMPUTE block.
A final version of the above program is shown below.
cynthia
[pre]
ods html file='c:\temp\shoes_acr.html' style=sasweb;
proc report data=sashelp.shoes nowd;
title 'ACROSS Example';
where region in ("Asia", "Canada") and
product in ("Sandal", "Boot", "Slipper");
column region subsidiary
product,(sales returns calc_col);
define region / group 'Region';
define subsidiary / group 'Subsidiary';
define product / across 'Product';
define sales / sum format=comma12.2 'Sales';
define returns / sum format=comma12.2 'Returns';
define calc_col / computed format=8.3 ;
rbreak after / summarize;
compute after region;
line ' ';
endcomp;
compute calc_col;
_c5_ = divide(_c3_ , _c4_);
_c8_ = divide(_c6_ , _c7_);
_c11_ = divide(_c9_ , _c10_);
endcomp;
run;
ods html close;
[/pre]