The SAS Output Delivery System and reporting techniques

Removing some column headers in PROC REPORT when using ACROSS option

Reply
N/A
Posts: 0

Removing some column headers in PROC REPORT when using ACROSS option

Dear all,

I have data that looks like

SHA mlk value

1 K 32
1 L 53
.
.
.
3 M 15

And I'm trying to use PROC REPORT to get a table that looks like

SHA K L M Total

1 32 53 10 95
2 12 4 20 36
3 5 10 15 20

However when I run the code

PROC REPORT DATA=IN0708.TFR0708_long;
COLUMNS 'SHA code'n mlk, value total;
DEFINE 'SHA code'n / GROUP;
DEFINE value / ANALYSIS SUM ;
DEFINE mlk /ACROSS ;
DEFINE total / COMPUTED FORMAT=nlmny15.0;
COMPUTE total;
total = _c2_ + _c3_ + _c4_;
ENDCOMP;
RUN;

I get
klm
SHA K L M Total
value value value
1 32 53 10 95
2 12 4 20 36
3 5 10 15 20


Is it possible to strip out the spanning header and the 'value' headers? I would be grateful for any help, thank you.

Chris
SAS Super FREQ
Posts: 8,864

Re: Removing some column headers in PROC REPORT when using ACROSS option

Posted in reply to deleted_user
Hi:
You can put a label (or remove a label) on the DEFINE statement. By default, since you have no label information on the DEFINE statement, your variable labels or names are used for the spanning headers. So, for example, to remove the label for the variable VALUE, you would have (quote, space, quote) as the label (the quoted string on the DEFINE statement):
[pre]
COLUMNS 'SHA code'n mlk, value total;
DEFINE 'SHA code'n / GROUP;
DEFINE value / ANALYSIS SUM ' ' ;
DEFINE mlk /ACROSS ;
DEFINE total / COMPUTED FORMAT=nlmny15.0;

[/pre]

If you selectively experiment with the ' ' (quote, space, quote) in your DEFINE statements, you will see that it is possible to remove
column headers that you do NOT want...or to assign headers that you DO want.

If you find that you have a row of empy cells when you assign blanks as the label for your header, you may want to rearrange the order of the variables you list in your COLUMN statement...and instead of what's above, try this, as well to see whether that gives you headers closer to what you want.
[pre]
COLUMNS 'SHA code'n value,mlk total;
[/pre]

Also, for future reference, this is a very handy forum posting that explains how to post questions and still maintain the indenting of code and of output that you want to show:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

cynthia
N/A
Posts: 0

Re: Removing some column headers in PROC REPORT when using ACROSS option

Posted in reply to Cynthia_sas
Hi Cynthia

Thank you very much for your reply and the reference for maintaining the correct formatting of code and output.

I've had some joy in implementing your suggestions, notably the switching round of mlk and value variables. However I now have three header rows, the top two of which are completley blank! I've tried other combinations but can't resolve this issue.

Its quite trivial to paste n copy the table into Word or other and manually delete the top two empty rows, buit I did rather think one of the major selling points of PROC REPORT was the ability to do all the formatting in code.

Thx, Chris
Respected Advisor
Posts: 3,799

Re: Removing some column headers in PROC REPORT when using ACROSS option

Posted in reply to deleted_user
If ACROSS does not give the formatting options that you require, TRANSPOSE first.
SAS Super FREQ
Posts: 8,864

Re: Removing some column headers in PROC REPORT when using ACROSS option

Posted in reply to deleted_user
Hi:
Although TRANSPOSE is an option, I do not believe you need it in this case. Look at this example constructed from some fake data. By the time all the changes are done, I only have 1 row of column headers in Report #3. (For example purposes, I just made a simple variable name for SHA code.)

If you have an ENTIRE row of blanks, PROC REPORT will suppress that row in destinations like HTML and RTF. The trick (as shown by the rearranging) is to get all the blank rows in one place. If you are still using the LISTING destination, instead of ODS RTF, you might want to consider using ODS RTF, where the blank rows -will- be suppressed, as opposed to the LISTING destination, where the blank rows will -not- be suppressed.

cynthia
[pre]
data stuff;
set sashelp.prdsale;
SHA = Country;
mlk = region;
value = actual;
run;

ods listing;
ods rtf file='scrunch.rtf';
ods html file='scrunch.html' style=sasweb;
proc report data=stuff nowd;
title '1) suppressing VALUE';
COLUMNS SHA mlk, value total;
DEFINE SHA / GROUP 'SHA code';
DEFINE value / ANALYSIS SUM ' ' f=comma7.;
DEFINE mlk /ACROSS ;
DEFINE total / COMPUTED FORMAT=nlmny15.0;
compute total;
total = _c2_ - _c3_;
endcomp;
run;

proc report data=stuff nowd;
title '2) rearranging MLK, VALUE';
COLUMNS SHA value,mlk total;
DEFINE SHA / GROUP 'SHA code';
DEFINE value / ANALYSIS SUM ' ' f=comma7.;
DEFINE mlk /ACROSS ;
DEFINE total / COMPUTED FORMAT=nlmny15.0;
compute total;
total = _c2_ - _c3_;
endcomp;
run;

proc report data=stuff nowd;
title '3) suppressing MLK';
COLUMNS SHA value,mlk total;
DEFINE SHA / GROUP 'SHA code';
DEFINE value / ANALYSIS SUM ' ' f=comma7.;
DEFINE mlk /ACROSS ' ';
DEFINE total / COMPUTED FORMAT=nlmny15.0;
compute total;
total = _c2_ - _c3_;
endcomp;
run;
ods _all_ close;
[/pre]
N/A
Posts: 0

Re: Removing some column headers in PROC REPORT when using ACROSS option

Posted in reply to Cynthia_sas
Thank you for your replies data _null_ and Cynthia.

The ODS solution worked perfectly - thank you. I can see maybe I need to buy a book on ODS, have you a recommendation Cynthia ??? ;-)
Ask a Question
Discussion stats
  • 5 replies
  • 803 views
  • 0 likes
  • 3 in conversation