Help using Base SAS procedures

proc report(use of across)

Reply
N/A
Posts: 0

proc report(use of across)

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
SAS Super FREQ
Posts: 8,743

Re: proc report(use of across)

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]
N/A
Posts: 0

Re: proc report(use of across)

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
Ask a Question
Discussion stats
  • 2 replies
  • 123 views
  • 0 likes
  • 2 in conversation