Hi:
When you have a computed column under an ACROSS variable, you must use ABSOLUTE column names in your assignment
statements. Consider this program:
[pre]
proc sort data=sashelp.shoes out=shoes;
by region subsidiary product;
where product in ('Slipper', 'Sandal') and
region in ('Asia', 'Pacific');
run;
ods html file='what_rep_does.html' style=sasweb;
proc report data=shoes nowd out=lookatme;
title 'This Report Shows missing for PCTRET item';
column region subsidiary product,(sales returns pctret);
define region / display;
define subsidiary / display;
define product/across;
define sales / analysis f=comma8.;
define returns/ analysis f=comma8.;
define pctret / computed;
compute pctret;
pctret = .;
endcomp;
run;
proc print data=lookatme;
title 'This is how PROC REPORT internally names the columns';
run;
ods html close;
[/pre]
If you ran the above code, the LISTING output from PROC REPORT (just for Asia) would look like this
(with PCTRET missing):
[pre]
This Report Shows missing for PCTRET item
Product
Sandal Slipper
Total Total Total Total
Region Subsidiary Sales Returns pctret Sales Returns pctret
Asia Bangkok 3,230 120 . . . .
Asia Bangkok . . . 3,019 127 .
Asia Seoul 4,978 105 . . . .
Asia Seoul . . . 149,013 2,941 .
[/pre]
and the PROC PRINT (just for Asia) would look like this:
[pre]
This is how PROC REPORT internally names the columns
Obs Region Subsidiary _C3_ _C4_ _C5_ _C6_ _C7_ _C8_ _BREAK_
1 Asia Bangkok $3,230 $120 . . . .
2 Asia Bangkok . . . $3,019 $127 .
3 Asia Seoul $4,978 $105 . . . .
4 Asia Seoul . . . $149,013 $2,941 .
[/pre]
Comparing the PROC PRINT and the PROC REPORT, for just the first report row, we can see that _C3_ is the SALES value
for Bangkok for sandals and that _c4_ is 120, which is the RETURNS value for Bangkok. Next _C5_ is for PCTRET (but is
missing because of my assignment statement. OK, now we see that under each across variable, I have 3 variables nested.
So _c3_, _c4_ and _c5_ belong to Sandal and _c6_, _c7_ and _c8_ belong to Slipper for every report row.
Now, because REGION and SUBSIDIARY had a usage of DISPLAY, I see 2 rows for Bangkok and 2 rows for Seoul. Probably not
what I want. I'd like to collapse those down to just 1 row for Asia/Bangkok and 1 row for Asia/Seoul. This can be easily
accomplished by changing the usage of REGION and SUBSIDIARY from DISPLAY to GROUP.
Oh, wait, there's that pesky PCTRET that still needs a value. OK...Here's how we're going to use those absolute column
names in our COMPUTE block:
[pre]
compute pctret;
_c5_ = _c4_ / _c3_;
_c8_ = _c7_ / _c6_;
endcomp;
[/pre]
Being able to LOOK at the column names that report uses for ACROSS reports is a handy use of the OUT= option. You don't
have to do it all the time, but when you're getting started with ACROSS, it's a good technique to use. Of course, if I
had more PRODUCTs, there would be more "sets" of 3 going across the top of the report.
After I make REGION and SUBSIDIARY GROUP items and change the compute block, this is what the rows for Asia now look like.
You can see I also assigned formats, etc. The final program is at the end of the post.
[pre]
This Report Uses ABSOLUTE column names/numbers
Product
Sandal Slipper
Total Total Total Total
Region Subsidiary Sales Returns pctret Sales Returns pctret
Asia Bangkok 3,230 120 3.72% 3,019 127 4.21%
Seoul 4,978 105 2.11% 149,013 2,941 1.97%
Asia 8,208 225 2.74% 152,032 3,068 2.02%
[/pre]
I added a BREAK to get a summary line for Asia. With EMPID, you probably won't do that (but you probably will want
EMPID to be a GROUP usage on the report).
There are a lot of good examples and explanations in this documentation which talks about using PROC REPORT in NOWD mode:
http://support.sas.com/documentation/onlinedoc/v82/techreport_p258.pdf
cynthia
[pre]
proc report data=shoes nowd;
title 'This Report Uses ABSOLUTE column names/numbers';
column region subsidiary product,(sales returns pctret);
define region / group f=$10.;
define subsidiary / group f=$10.;
define product/across;
define sales / analysis f=comma8.;
define returns/ analysis f=comma8.;
define pctret / computed f=percent8.2;
compute pctret;
_c5_ = _c4_ / _c3_;
_c8_ = _c7_ / _c6_;
endcomp;
break after region / summarize;
compute after region;
line ' ';
endcomp;
run;
[/pre]