Proc Report: Computed variable with Across

[mnew]

Hi Experts:

I'm trying to convert a proc report group variable to across variable but could not get my compute block working. To illustrate my trouble without typing up my data, the code below used sashelp.cars. I know it might not make sense to average MPG_City and MPG_Highway...In reality I need to calculate an average of 3 monthly numeric variables for each category, (e.g. a Q1 average sales amount for each region).

Thank you!

Here is what I wrote for the group layout that worked.

Proc report data=sashelp.cars nowd headline headskip;
Column Origin MPG_City MPG_Highway Avg_MPG;
Format MPG_City MPG_Highway 5.2;
Define Origin / Group;
Define MPG_City / Mean 'MPG /City';
Define MPG_Highway / Mean 'MPG /Highway';
Define Avg_MPG /Computed 'MPG / Avg' Format=5.2;
Compute Avg_MPG;
Avg_MPG = Mean(MPG_City.mean, MPG_Highway.mean);
Endcomp;
Run;

Below is the Across layout that did not work, along with error messages.

Proc report data=sashelp.cars nowd headline headskip;
Column Origin, (MPG_City MPG_Highway Avg_MPG);
Format MPG_City MPG_Highway 5.2;
Define Origin / Across;
Define MPG_City / Mean 'MPG /City';
Define MPG_Highway / Mean 'MPG /Highway';
Define Avg_MPG /Computed 'MPG / Avg' Format=5.2;
Compute Avg_MPG;
Avg_MPG = Mean(MPG_City.mean, MPG_Highway.mean);
Endcomp;
Run;
Error message:
ERROR: The variable type of MPG_CITY.MEAN is invalid in this context.
ERROR: The variable type of MPG_HIGHWAY.MEAN is invalid in this context.
NOTE: The preceding messages refer to the COMPUTE block for Avg_MPG.
NOTE: Will not run due to compilation errors.

[Cynthia_sas]

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]

[mnew]

Thank you! I was able to correct my codes. I did research the archive before posting the question. Didn't comprehend the concept of absolute column numbers and why they were needed when I first read some replies. Your detailed explanation really helped.