topic Re: using formats with styles in ODS and Base Reporting

using formats with styles

wkossack_nspirehealth_com — Wed, 26 Jun 2013 22:49:25 GMT

I have another question

I have a proc format

proc format;

value colormis low-0='black' 0-high='red';

value colorstd low- -.1='green' -.1-.25='black' .25-high='red';

value mispoint low-1='black' 1-2='red' 2-high='red';

value countpoint low-1='black' 1-2='red' 2-high='red';

is it possible to apply a format against a style?

For example

style colormis from data/ tagattr='type:numeric format=f4.0' foreground=format(colormis.);

I need to be able to do traffic lighting on the cells in excel

For example I was using the define statement define countmissing /'Visits*With*No Data' style={foreground=colormis.};

to color code cells based on the value

Re: using formats with styles

Cynthia_sas — Thu, 27 Jun 2013 02:34:39 GMT

Hi, I am not sure what you mean when you ask "against a style" -- I guess you mean a style template? The STYLE statement that you show:

style colormis from data/

tagattr='type:numeric format=f4.0'

foreground=format(colormis.);

How would you envision using the COLORMIS style element? Even if you make a custom style element, you have to link the custom style element to your report item in a style override. I don't usually specify TAGATTR or FOREGROUND in my style template, if I am doing trafficlighting because the STYLE template is supposed to be "data neutral" -- it is used for ANY data--if you used this style template, for example with a data set of names, addresses, cities and states, then it would be inappropriate to use the COLORMIS style element (with your TAGATTR of numeric) with your character variables. So then you'd need a different style template for the tables where you didn't want to use COLORMIS or used a different COLORMIS.

You should be getting an error on your TAGATTR specification, anyway. The method of specifying formats is to use type: and format: (not format=). See this Tech Support note for examples of FORMAT: 38105 - How can I control the formatting of my output when exporting output to Excel using ODS? And to use TYPE: and FORMAT: together, you need to be careful about using the right syntax. Usually, if the format or type is bad, excel won't open the file and will complain. And, you have to use an Excel format, not a SAS format. Are you sure that F4.0 is an Excel format?

In my SAS Global Forum paper, on page 8, I show how to format both positive and negative numbers using Excel custom format syntax. I do not recall F4.0 being an Excel format when I was researching my paper. http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

There are 2 basic ways to do color coding (traffic lighting) with the reporting procedures, Excel just adds an extra twist into the mix #3 below.
1) Use traffic-lighting in PRINT, REPORT and TABULATE with a user-defined format and a STYLE= override (HTML, RTF, PDF, EXCELXP)

2) Do traffic-lighting in PROC REPORT using CALL DEFINE (with or without a user-defined format) in a COMPUTE block (HTML,RTF, PDF, EXCELXP)

3) If your DESTINATION is MSOFFICE2K or EXCELXP, use STYLE= overrides to pass a Microsoft format directly from SAS to Excel.

A style template is not one of the "basic" ways. I would not use a style template for "data-specific" values. It is too hard to implement, nearly impossible. The code below works for me, once I make some fake numbers for the report. I would not use a style template for this. Remember that a style template could be used with PROC REG or PROC GLM or PROC UNIVARIATE -- none of those procedures would support trafficlighting the way you want to implement it. A style template has visibility of the main elements on the report (title, column headers, data cells, etc), but think of the style template as not being able to "see down" into the data values on te report.

There may be some changes to style templates in the future that would allow visibility of data values using more CSS-like syntax, but I would not count on that for what you want to do today. The program below illustrates output tailored for Excel. It uses the Microsoft format in #1 example below for the minus sign and number of decimal places, but a SAS format for the colors. #2 example below uses a Microsoft format for EVERYTHING. This means that no other destination would receive color changes for these items.

cynthia

proc format;
      value colormis low-0='black' 0-high='red';
run;

** make some fake data with 0 for some values;
data class;
set sashelp.class;
fakenum = ranuni(0);
if sex = 'M' then fakenum = fakenum*10;
else if sex = 'F' then fakenum=fakenum*-1;
if name in ('Joyce', 'Barbara', 'Alice','Janet')
     then fakenum = 0;
run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\test_colormis.xml'
    style=sasweb;

proc report data=class nowd;
** Use Format for FAKENUM column;
column ('1) Use SAS format for color, Excel TAGATTR for negative' age name sex fakenum);
define age / order;
define name / order;
define sex / display;
define fakenum / display
         style(column)={tagattr='format:#,##0.000;-#,##0.000;#,##0.0;' foreground=colormis.};
run;

proc report data=class nowd;
** Use Excel coding for FAKENUM column;
column ('2) Use Excel format for color and decimals -- 0 is blue' age name sex fakenum);
define age / order;
define name / order;
define sex / display;
define fakenum / display
    style(column)={tagattr='format:[Red]#,##0.000;[Black]-#,##0.000;[Blue]#,##0.000;'};
run;

ods tagsets.excelxp close;

cynthia

Re: using formats with styles

wkossack_nspirehealth_com — Thu, 27 Jun 2013 02:46:12 GMT

thanks

I thought of changing my proc print to a proc report on my way home. This is another case of something that worked in 9.2 that does not in 9.3

I'll tackle it in the morning with a fresh brain

Re: using formats with styles

Cynthia_sas — Thu, 27 Jun 2013 14:56:32 GMT

Hi:

Are you saying that you had a STYLE= override that worked in PROC PRINT in 9.2 that doesn't work in 9.3, then that would be something to bring to the attention of Tech Support.

However, I do not see how your style template approach would ever have worked in 9.2 or 9.3. As you coded this:

style colormis from data/

tagattr='type:numeric format=f4.0'

foreground=format(colormis.);

it would not have worked in either 9.2 or 9.3. So I'm confused about exactly WHAT worked in 9.2 that doesn't work in 9.3. There is syntax for a TABLE template that you might have tried in the past, but PROC PRINT doesn't use a TABLE template and the syntax snippet that you showed is STYLE template syntax. Since you didn't show any other code, it is impossible to comment or offer suggestions. In order to help anyone help you, it is important to see all your code. If you don't want to share code or data because it is proprietary or confidential, then use a SASHELP dataset to illustrate the problem/question.

cynthia

Re: using formats with styles

wkossack_nspirehealth_com — Thu, 27 Jun 2013 15:30:23 GMT

I tried your code

proc report data=class nowd;

** Use Format for FAKENUM column;

column ('1) Use SAS format for color, Excel TAGATTR for negative' age name sex fakenum);

define age / order;

define name / order;

define sex / display;

define fakenum / display

style(column)={tagattr='format:#,##0.000;-#,##0.000;#,##0.0;' foreground=colormis.};

run;

proc report data=class nowd;

** Use Excel coding for FAKENUM column;

column ('2) Use Excel format for color and decimals -- 0 is blue' age name sex fakenum);

define age / order;

define name / order;

define sex / display;

define fakenum / display

style(column)={tagattr='format:[Red]#,##0.000;[Black]-#,##0.000;[Blue]#,##0.000;'};

and it returned

WARNING: In Event: style_over_ride, Invalid Expression:

ERROR: WHERE clause operator requires numeric variables.

Re: using formats with styles

wkossack_nspirehealth_com — Thu, 27 Jun 2013 16:59:56 GMT

I got it working with the help from Chevell

the problem with old code is that there can be a bunch of old included code. We found where an old 9.2 template was hard coded in.

Once that was done the approach using

proc format;

value colormis low-0='black' 0-high='red';

value colorstd low- -.1='green' -.1-.25='black' .25-high='red';

value mispoint low-1='black' 1-2='red' 2-high='red';

value countpoint low-1='black' 1-2='red' 2-high='red';

run;

define missing /'Missing*Time*Point' style=[foreground=mispoint.];

define LTF_4 /'Visit*4*Stopped' format=f4.0 style=[foreground=colormis.];

define LTF_8 /'Visit*8*Stopped' format=f4.0 style=[foreground=colormis.];

worked

Re: using formats with styles

wkossack_nspirehealth_com — Thu, 27 Jun 2013 17:16:49 GMT

ah left out

style mispoint from data/ foreground=mispoint.;

style colormis from data/ tagattr='type:numeric format=f4.0' foreground=colormis.;

style countpoint from data/ tagattr='type:numeric format=f4.0' foreground=countpoint.;

style colorstd from data / tagattr='type:numeric format=f4.2' foreground=colorstd.;

actually the format in the define is not needed