BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
data_null__
Jade | Level 19
If ACROSS does not give the formatting options that you require, TRANSPOSE first.
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
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 ??? 😉

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4027 views
  • 0 likes
  • 3 in conversation