proc report(use of across)

deleted_user — Sat, 01 Aug 2009 11:26:10 GMT

In proc report when we define any variable as across,in that across variable can we have new computed variable as well.if yes how? E.G

proc report data = abcd;
column empid type,(salary bonus percentage_bonus);
define empid/display;
define type/across;
define salary/analysis;
define bonus/analysis;
define percentage_bonus/computed format percent10.2;
compute percentage_bonus;
percentage_bonus = ((salary-bonus)/salary) * 100;
endcomp;
quit;

thanks

Re: proc report(use of across)

Cynthia_sas — Sat, 01 Aug 2009 16:13:44 GMT

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]

Re: proc report(use of across)

deleted_user — Mon, 03 Aug 2009 11:40:25 GMT

Thanks a lot Cynthia....Actually i am new to SAs so thats y i ma asking these type of questions..Its just been one month that i am using sas..Again thanks

topic proc report(use of across) in SAS Procedures

proc report(use of across)

Re: proc report(use of across)

Re: proc report(use of across)