The SAS Output Delivery System and reporting techniques

Merge values for the grouped column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Merge values for the grouped column

Hi,

 

I am trying to get the attached reporting structure using Proc Report. I face problem in merging the 'region' column. 

 

I used the below code after refering previous posts but  couldn't get the desired output.

 

proc report data=salesnowindows HEADLINE style(header)={font_weight=bold};
title1 '  ' ;
title2 JUSTIFY=left font=ARIAL height=10pt ' ' ;
title3 JUSTIFY=left font=ARIAL height=10pt ' ';
title4 JUSTIFY=left font=ARIAL height=10pt ' ';
title5 JUSTIFY=left font=ARIAL height=10pt '';


column region Reporting_Product sales returns overhead1 overhead2 overhead3 overhead4 ;


define region / group
style(header)={vjust=middle}
style(column)=[vjust=middle just=left];

 

define Reporting_Product / group 'Product' ;

define sales/display analysis sum style={tagattr='format:0.0,,' };
define returns/display analysis sum style={tagattr='format:0.0,,' };
define overhead1 /display analysis sum style={tagattr='format:0.0,,' };

define overhead2 /display analysis sum style={tagattr='format:0.0,,' };
define overhead3 /display analysis sum style={tagattr='format:0.0,,'};
define overhead4 /display analysis sum style={tagattr='format:0.0,,'};
define overhead5 /display analysis sum style(Column)={tagattr='format:0.0,,' font_weight=bold};

rbreak after /summarize;

compute after;
value='Total';
endcomp;

run;


Accepted Solutions
Solution
‎02-07-2017 06:31 PM
SAS Super FREQ
Posts: 8,740

Re: Merge values for the grouped column

[ Edited ]

Hi, You posted a .DOCX file, but you are using TAGATTR overrides, which seem to indicate you want Excel output. Do you want ODS EXCEL or ODS RTF or???

You did not post any data nor post any ODS statements.

It is not clear what you mean by "merging" the REGION column. Have you investigated the SPANROWS option (that goes in the PROC REPORT statement)? It will cause GROUP or ORDER items to span the other report rows.

And, some comments, feedback:
1) you do not need HEADLINE because that is a LISTING only option.
2) If you want the values for each REGION to appear as though they are header cells (as indicated by your screen shot), then you will want
style(column)=Header{vjust=middle just=left} for your DEFINE REGION statement.
3) you have OVERHEAD5 as a define statement with a usage of display, but it is not listed in the COLUMN statement. What messages to you see in the SAS log? And the correct usage is to have style(column) for the TAGATTR overrides. Why do you have it for OVERHEAD5, but not for the other OVERHEAD items?
4) From your screen shot, it appears as though you want to rotate the values for each REGION, this may or may not be possible depending on your destination. But since you did not post any of your destination statements or data, no one can test any code.
5) in your PROC REPORT statement, you have
data=salesnowindows
which looks like you are trying to use the NOWINDOWS or NOWD option. However, that is the default in effect with SAS 9.4 so you might not need that option, and generally, there is a space between the data set name and the option. If your data set is named WORK.SALES, then I would expect you to have an error because SALESNOWINDOWS data is not found.
6) I do not understand the use of the variable VALUE in the COMPUTE AFTER block. YOu do not have VALUE as a computed item and you do not have a variable named VALUE in the COLUMN statement. Typically, if you want to see some kind of label on the Grand Total line from the RBREAK statement, you would have an assignment statement something like:
region = 'Total';

Most useful with questions like this is to show all your code, including ODS statements and relevant options and to include a DATA step program that makes data or use on of the SASHELP datasets to illustrate your question. For example, SASHELP.SHOES has variables REGION, PRODUCT, SALES, INVENTORY and RETURNS and you could certainly use that instead to provide people some idea of what your question is.

Overhead1, 2, 3, 4 and 5 might be variables in your data, but they are generally irrelevant to a question about REGION. For example, try this:

show_spanrows.png

  As you can see, it is not EXACTLY like the data you described, but general enough to give the idea of the type of GROUPed report you want and how you want to see Total on the summary line. As you can see, there are differences between the RTF output and the default HTML output. But since you did not show any ODS statements, it's hard to do more than make these few observations and offer an alternative example.

 

cynthia

View solution in original post


All Replies
Solution
‎02-07-2017 06:31 PM
SAS Super FREQ
Posts: 8,740

Re: Merge values for the grouped column

[ Edited ]

Hi, You posted a .DOCX file, but you are using TAGATTR overrides, which seem to indicate you want Excel output. Do you want ODS EXCEL or ODS RTF or???

You did not post any data nor post any ODS statements.

It is not clear what you mean by "merging" the REGION column. Have you investigated the SPANROWS option (that goes in the PROC REPORT statement)? It will cause GROUP or ORDER items to span the other report rows.

And, some comments, feedback:
1) you do not need HEADLINE because that is a LISTING only option.
2) If you want the values for each REGION to appear as though they are header cells (as indicated by your screen shot), then you will want
style(column)=Header{vjust=middle just=left} for your DEFINE REGION statement.
3) you have OVERHEAD5 as a define statement with a usage of display, but it is not listed in the COLUMN statement. What messages to you see in the SAS log? And the correct usage is to have style(column) for the TAGATTR overrides. Why do you have it for OVERHEAD5, but not for the other OVERHEAD items?
4) From your screen shot, it appears as though you want to rotate the values for each REGION, this may or may not be possible depending on your destination. But since you did not post any of your destination statements or data, no one can test any code.
5) in your PROC REPORT statement, you have
data=salesnowindows
which looks like you are trying to use the NOWINDOWS or NOWD option. However, that is the default in effect with SAS 9.4 so you might not need that option, and generally, there is a space between the data set name and the option. If your data set is named WORK.SALES, then I would expect you to have an error because SALESNOWINDOWS data is not found.
6) I do not understand the use of the variable VALUE in the COMPUTE AFTER block. YOu do not have VALUE as a computed item and you do not have a variable named VALUE in the COLUMN statement. Typically, if you want to see some kind of label on the Grand Total line from the RBREAK statement, you would have an assignment statement something like:
region = 'Total';

Most useful with questions like this is to show all your code, including ODS statements and relevant options and to include a DATA step program that makes data or use on of the SASHELP datasets to illustrate your question. For example, SASHELP.SHOES has variables REGION, PRODUCT, SALES, INVENTORY and RETURNS and you could certainly use that instead to provide people some idea of what your question is.

Overhead1, 2, 3, 4 and 5 might be variables in your data, but they are generally irrelevant to a question about REGION. For example, try this:

show_spanrows.png

  As you can see, it is not EXACTLY like the data you described, but general enough to give the idea of the type of GROUPed report you want and how you want to see Total on the summary line. As you can see, there are differences between the RTF output and the default HTML output. But since you did not show any ODS statements, it's hard to do more than make these few observations and offer an alternative example.

 

cynthia

Occasional Contributor
Posts: 6

Re: Merge values for the grouped column

Thanks Cynthia, yes its ODS EXCEL. SPANROWS worked fine in displaying the region as single merged cell in Excel . I have the following requests.

 

1. How to rotate the values.

2. I need to insert a blank column in Column A (First column for better appeal).

3. How to outline the title 1 as in the attachment (Row 2).

4. How to get the sub-total in the column C (product column) without displaying the region name in the column B(region column). I tried using  the below but got Region name displayed in the column B.

 

compute after Region;
Reporting_Product='Sub-total';
endcomp;

 

I have pasted the code below . column names are made up for organizational security reasons.

 


ODS TAGSETS.EXCELXP OPTIONS ( Sheet_Name = 'Exposures'
Absolute_Column_Width = '15,20,12,12,8,8,8,8,8,8'
sheet_interval='none'
Orientation = 'portrait'
HIDDEN_COLUMNS='none'
Row_Heights = '20,20,15,15,15,15,15'
zoom='85'
Center_Horizontal = 'yes'
);

proc report data=salesdata spanrows nowd style(header)={font_weight=bold background=lightgreen};
title1 ' Appendix 1' ;
title2 JUSTIFY=left font=ARIAL height=10pt ' ' ;
title3 JUSTIFY=left font=ARIAL height=10pt ' title2' ;
title4 JUSTIFY=left font=ARIAL height=10pt ' title3';
title5 JUSTIFY=left font=ARIAL height=10pt ' title4';
title6 JUSTIFY=left font=ARIAL height=10pt ' title 5';
title7 JUSTIFY=left font=ARIAL height=10pt ' additional line';

 

column Region Reporting_Product Sales Returns Overhead1 Overhead2 Overhead3 Overhead4 Overhead5 ;


define Region / group ' ' style(column)=Header{vjust=middle just=left background=lightgreen}
/*style= vcentre
style(header)={vjust=middle}
style(column)=[vjust=middle just=left]*/;


define Reporting_Product / group 'Product Asset Group' ;

define Sales /display analysis sum style={tagattr='format:0.0,,' };
define Returns /display analysis sum style={tagattr='format:0.0,,' };
define Overhead1 /display analysis sum style={tagattr='format:0.0,,' };

define Overhead2 /display analysis sum style={tagattr='format:0.0,,' };
define Overhead3 /display analysis sum style={tagattr='format:0.0,,'};
define Overhead4 /display analysis sum style={tagattr='format:0.0,,'};
define Overhead5 /display analysis sum style(Column)={tagattr='format:0.0,,'};

break after Region/ style={tagattr='format:0.0,,' font_weight=bold} summarize;


rbreak after / summarize style={tagattr='format:0.0,,' font_weight=bold};

 

compute after Region;
Region='Sub-total';
endcomp;

 

compute after;
Region='Total';
endcomp;

 

run;

SAS Super FREQ
Posts: 8,740

Re: Merge values for the grouped column

Hi:
   I'm not in a place to run code, now, or write code, but you should be able to figure out much of what you want to do

  Here's what you asked and my comments:
1. How to rotate the values. -- you will have to look up whether there is an MSO format you can use with TAGATTR and TAGSETS.EXCELXP to perform the rotation on the REGION value -- I'm not sure there is one or there might be, but you have to search for it in the doc. I seem to remember a ROTATE attribute for the TAGATTR suboption (illustrated here: https://support.sas.com/rnd/base/ods/odsmarkup/TipSheet_ods_xl_xp.pdf

2. I need to insert a blank column in Column A (First column for better appeal). By default, when you use TAGSETS.EXCELXP, all output starts in column A in Excel. You could make a "fake" variable that would be blank to force everything to start with column B, but that is hardly worth it. You have Center_horizontal turned on for printing so when/if they print, the output will be centered. If you add a fake column A, then with your Center horizontal setting, you will also be centering the fake column, which might make the output look uncentered. I do not fiddle with Excel this much so I don't have examples of these techniques, but other people might..

3. How to outline the title 1 as in the attachment (Row 2). I think that you would need to change the STYLE template you're using for this output and, I should warn you that, frequently, ODS ignores borderlines around titles, so this might not work as you envision. I believe you have to change the TitleContainer element and the SystemTitle element, but you'll have to be careful because the higher numbered titles inherit attributes from the SystemTitle element, so you will have to define one style for Title1 and another style for the others. But again, I'm not sure that Excel will respect the border on the title.

4. How to get the sub-total in the column C (product column) without displaying the region name in the column B(region column). I tried using  the below but got Region name displayed in the column B. Please avoid the Excel terminology to explalin what you need to do with SAS code. Right now, by default, when rendered in Excel, the output will have REGION in Column A and will have PRODUCT in Column B. You can't change that yet. So there's no point, yet, in confusing the issue by referencing the columns by the names you WANT them to be. And it's also confusing to the new programmer because the columns you deal with in SAS are named REGION and PRODUCT not Column A, Column B and Column C.

You can move the "Total" string to the Product column. Assuming you have a BREAK AFTER REGION statement, you can use a COMPUTE block at the break to change the string at the break. Right now, you have REGION='Sub-total' and in your new compute block, you would have PRODUCT='Sub-total'.

There are examples of this kind of COMPUTE block in previous forum postings. I'm sure you'll find some if you search for them. The PROC REPORT documentation has good examples, too.

cynthia

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 251 views
  • 2 likes
  • 2 in conversation