topic Re: Referencing Labels in ODS/Proc Report in ODS and Base Reporting

Referencing Labels in ODS/Proc Report

MikeCa — Mon, 15 Nov 2010 18:25:56 GMT

How do I reference in the proc report DEFINE statement the lable name attribute? I've tried qbo and OKC Total none seem to work on my background style. Thanks

Label
qbo label='OKC Total'

Proc Stmt
proc report data=GLBAL NOWD
style(header)=[ BACKGROUND = yellow
FONT_WEIGHT = bold];
define qbo / style(column)=[background = CX4D7EBF];

Re: Referencing Labels in ODS/Proc Report

ArtC — Tue, 16 Nov 2010 02:33:04 GMT

Have you tried changing the style component from COLUMN to HEADER in the DEFINE statement?

Re: Referencing Labels in ODS/Proc Report

MikeCa — Tue, 16 Nov 2010 03:12:31 GMT

My purpose is to have the whole column color shaded not just the header. I'm perplexed how to reference the whole column. The label definition is as follows:

qbo label='OKC Total'

No errors are noted in the log ?

errorlog
3408 ods tagsets.excelxp file="I:\FINANCE\QShare\2010\Stmt201010s\GLTrialBal.xml" style=minimal
3409 options(sheet_name='GLBAL'
3410 sheet_interval='proc'
3411 frozen_headers='1'
3412 autofit_height='yes'
3413 absolute_column_width='5,4,25,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9'
3414 autofilter='yes');
NOTE: Writing TAGSETS.EXCELXP Body file: I:\FINANCE\QShare\2010\Stmt201010s\GLTrialBal.xml
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.116, 08/25/10). Add options(doc='help') to the ods statement for more information.
3415
3416 proc report data=GLBAL NOWD
3417 style(header)=[ BACKGROUND = yellow
3418 FONT_WEIGHT = bold];
3419 define qbo / style(header)={background = CX90D9D7}; /*OKC Total*/
3420 define QBS / style(column)={background = CX90D9D7}; /*QEP Uinta*/
3421 define QBT / style(column)={background = CXB1E599}; /*Total QEP*/
3422 define bqet / style(column)={background = CXB1E599}; /*QET Total*/
3423 define bqgm / style(column)={background = CXB1E599}; /*QGM Total*/
3424 define BAL / style(column)={background = CXB1E599}; /*QMR Total*/
3425 define _NUMERIC_ / style={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
3426 run;

NOTE: There were 1686 observations read from the data set WORK.GLBAL.
NOTE: PROCEDURE REPORT used (Total process time):
real time 16.18 seconds
cpu time 16.17 seconds

Re: Referencing Labels in ODS/Proc Report

Cynthia_sas — Tue, 16 Nov 2010 04:12:04 GMT

Hi:
I'm not sure exactly what you want. PROC REPORT has 2 main "areas" that you can "touch" with STYLE= overrides. You cannot really reference the whole column with one STYLE= override. You can override the STYLE(HEADER) and you can override the STYLE(COLUMN) -- you can make them have the same background color or have different background colors.

The program below produces the 1st column with a blue header and blue data cells; the second column has a green header and green data cells and the last 3 columns on the report use the defaults set in the PROC REPORT statement.

As for your question about how to "reference" labels. I'm not sure here what you mean. You mean you want PROC REPORT to -use- the variable label??? Or you want to specify your own label?? Or????

The program below is not as complex as your program, but hopefully there is enough there to answer your two questions.

cynthia
[pre]

ods tagsets.excelxp file='c:\temp\use_label_color.xls'
style=minimal
options(sheet_name='GLBAL'
sheet_interval='proc'
frozen_headers='1'
autofit_height='yes'
absolute_column_width='15,15,15,15,10'
autofilter='yes');

proc report data=sashelp.class nowd
style(header)={background=yellow font_weight=bold}
style(column)={background=pink};
column name sex age height weight;
define name / 'OKC Total' style(header)={background = CX90D9D7}
style(column)={background = CX90D9D7};
define sex / 'QEP Uinta' style(header)={background = CXB1E599}
style(column)={background = CXB1E599};
define age / 'Total QEP';
define height / 'QET Total';
define weight / 'QGM Total';
run;

ods _all_ close;
[/pre]

Re: Referencing Labels in ODS/Proc Report

MikeCa — Tue, 16 Nov 2010 15:59:06 GMT

I've tried to mimick your example code to my situation and included a copy of error log, see below. It still is not formatting the specific columns qbo 'OKC Total' the color shade I'm requesting in the code? I don't understand why -- I was thinking I have been referencing the attribute incorrectly -- thats why I was asking how to reference the column attribute? All I know is its still not working -- I'm a beginner at this so any other hints are very much appreciated!

ods tagsets.excelxp file="I:\FINANCE\QShare\2010\Stmt201010s\GLTrialBal.xml" style=minimal
options(sheet_name='GLBAL'
sheet_interval='proc'
frozen_headers='1'
autofit_height='yes'
absolute_column_width='5,4,25,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9'
autofilter='yes');

proc report data=GLBAL NOWD
style(header)={ BACKGROUND = yellow
FONT_WEIGHT = bold}
style(column)={BACKGROUND = PINK};
COLUMN EXCa EXCm DESC b1 b11 qbo b9 b2 b3 b62 b63 b67 qbs qbt b12 b27 b14 b25 bqet b16 b76 b78 bqgm b75 bal b77 b79;
define qbo / 'OKC Total' style(header)={background = CX90D9D7}
style(column)={background = CX90D9D7}; /*OKC Total*/
define QBS / 'Uinta Total' style(header)={background = CX90D9D7}
style(column)={background = CX90D9D7}; /*QEP Uinta*/
define QBT / 'QEP Total' style(header)={background = CXB1E599}
style(column)={background = CXB1E599}; /*Total QEP*/
define bqet / 'QET Total' style(header)={background = CXB1E599}
style(column)={background = CXB1E599}; /*QET Total*/
define bqgm / 'QGM Total' style(header)={background = CXB1E599}
style(column)={background = CXB1E599}; /*QGM Total*/
define BAL / 'QMR Total' style(column)={background = CXB1E599}
style(column)={background = CXB1E599}; /*QMR Total*/
define _NUMERIC_ / style={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
run;

Error Log
326 dm 'log' clear;
3327 dm 'odsresults' clear;
3328 *ODS tagsets.excelxp file="c:\test.xml" options(doc="help");
3329
3330 * Tells SAS where to find the ExcelXP tagset;
3331 ods tagsets.excelxp file="I:\FINANCE\QShare\2010\Stmt201010s\GLTrialBal.xml" style=minimal
3332 options(sheet_name='GLBAL'
3333 sheet_interval='proc'
3334 frozen_headers='1'
3335 autofit_height='yes'
3336 absolute_column_width='5,4,25,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9'
3337 autofilter='yes');
NOTE: Writing TAGSETS.EXCELXP Body file: I:\FINANCE\QShare\2010\Stmt201010s\GLTrialBal.xml
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.116, 08/25/10). Add options(doc='help') to the ods statement for more information.
3338
3339 proc report data=GLBAL NOWD
3340 style(header)={ BACKGROUND = yellow
3341 FONT_WEIGHT = bold}
3342 style(column)={BACKGROUND = PINK};
3343 COLUMN EXCa EXCm DESC b1 b11 qbo b9 b2 b3 b62 b63 b67 qbs qbt b12 b27 b14 b25 bqet b16 b76 b78 bqgm b75 bal b77 b79;
3344 define qbo / 'OKC Total' style(header)={background = CX90D9D7}
3345 style(column)={background = CX90D9D7}; /*OKC Total*/
3346 define QBS / 'Uinta Total' style(header)={background = CX90D9D7}
3347 style(column)={background = CX90D9D7}; /*QEP Uinta*/
3348 define QBT / 'QEP Total' style(header)={background = CXB1E599}
3349 style(column)={background = CXB1E599}; /*Total QEP*/
3350 define bqet / 'QET Total' style(header)={background = CXB1E599}
3351 style(column)={background = CXB1E599}; /*QET Total*/
3352 define bqgm / 'QGM Total' style(header)={background = CXB1E599}
3353 style(column)={background = CXB1E599}; /*QGM Total*/
3354 define BAL / 'QMR Total' style(column)={background = CXB1E599}
3355 style(column)={background = CXB1E599}; /*QMR Total*/
3356 define _NUMERIC_ / style={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
3357 run;

NOTE: There were 1686 observations read from the data set WORK.GLBAL.
NOTE: PROCEDURE REPORT used (Total process time):
real time 19.92 seconds

Re: Referencing Labels in ODS/Proc Report

MikeCa — Tue, 16 Nov 2010 16:25:02 GMT

Okay, I deleted the following part of the code and it works!!!! Yeah!! There must be a better way to format the values to this format. Maybe in the global Header/column statements. Thanks for your hints and suggestions. I'm still learning.

define _NUMERIC_ / style={tagattr='format:#,##0.00_);[Red](#,##0.00)'};

Re: Referencing Labels in ODS/Proc Report

Cynthia_sas — Tue, 16 Nov 2010 18:39:35 GMT

Hi:
As I said, PROC REPORT only allows you to "touch" certain "areas" of the report. It would be inappropriate, for example, for your TAGATTR specification to be applied to the HEADER cells for all your numeric columns.

So when I showed style(header) and style(column), I was trying to show you that you needed to provide PROC REPORT with the area that you wanted to alter. REPORT syntax without an area might work for something trivial like background color, but why not just use an area -- in parentheses??

The syntax below worked for me. I picked a simpler Microsoft format for my TAGATTR, but I format ALL the numeric variables in SASHELP.CLASS by appropriately using the STYLE(COLUMN)= override. If you used the TAGATTR in your PROC REPORT statement, then I believe you might run into issues because the TAGATTR would be inappropriate for any character variables. You have these variables in your COLUMN statement:
[pre]
COLUMN EXCa EXCm DESC b1 b11 qbo b9 b2 b3 b62 b63 b67 qbs qbt b12 b27
b14 b25 bqet b16 b76 b78 bqgm b75 bal b77 b79;
define qbo / 'OKC Total' style(header)={background = CX90D9D7}
style(column)={background = CX90D9D7}; /*OKC Total*/
define QBS / 'Uinta Total' style(header)={background = CX90D9D7}
style(column)={background = CX90D9D7}; /*QEP Uinta*/
[/pre]

If EXCa, EXCm, DESC, for example, are character variables, then the TAGATTR would be incorrect for them -- I don't think Excel is very graceful about using a numeric format for a character string.

And, on a slightly different note, if QBO and QBS are numeric variables, then they would also be covered in the DEFINE _NUMERIC_ statement -- so it's like you have 2 conflicting statements for QBO and QBS -- their individual DEFINE statements and the all-encompassing DEFINE for _NUMERIC_. Note that in my code, I do not have a separate DEFINE statement for AGE and then a DEFINE statement for _NUMERIC_ -- I only have the DEFINE for _NUMERIC_. You may actually have to explicitly specify your TAGATTR for QBO and QBS and any other numeric variables that you want to have use that Microsoft format.

It may be possible to play around with different placements for your DEFINE statement to end up getting what you want, but I would recommend
1) that you take a step back and start with using the correct STYLE(HEADER) or STYLE(COLUMN) syntax
2) take out the DEFINE _NUMERIC_ statement and use STYLE(HEADER) and STYLE(COLUMN) in the PROC REPORT statement to set color defaults for all the columns on the report (colors and fonts are OK to apply in the PROC REPORT statement, because it doesn't matter whether your items are character or numeric)
3) use DEFINE statements with usages for all your variables. It is hard to understand what items are DISPLAY, ORDER, or GROUP items on your report. Then, use STYLE(HEADER) and STYLE(COLUMN) appropriately on each DEFINE statement to make other changes -- you might not need any other changes for your character variables, but here you might want to use TAGATTR for your numeric variables.

cynthia
[pre]
ods tagsets.excelxp file='c:\temp\use_label_color2.xls'
style=minimal
options(sheet_name='GLBAL'
sheet_interval='proc'
frozen_headers='1'
autofit_height='yes'
absolute_column_width='15,15,15,15,10'
autofilter='yes');

proc report data=sashelp.class nowd
style(header)={background=yellow font_weight=bold}
style(column)={background=pink};
column name sex _numeric_;
define name / 'OKC Total' style(header)={background = CX90D9D7}
style(column)={background = CX90D9D7};
define sex / 'QEP Uinta' style(header)={background = CXB1E599}
style(column)={background = CXB1E599};
define _numeric_/
style(header)={font_style=italic background=purple foreground=pink}
style(column)={background=cxdddddd tagattr='Format:0000.00'};
label age= 'Total QEP'
height = 'QET Total'
weight = 'QGM Total';
run;

ods _all_ close;
[/pre]

Re: Referencing Labels in ODS/Proc Report

MikeCa — Tue, 16 Nov 2010 18:59:18 GMT

Thank you for the help and education!