BookmarkSubscribeRSS Feed
wkossack_nspirehealth_com
Calcite | Level 5

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

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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

wkossack_nspirehealth_com
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

cynthia

wkossack_nspirehealth_com
Calcite | Level 5

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.

wkossack_nspirehealth_com
Calcite | Level 5

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

wkossack_nspirehealth_com
Calcite | Level 5

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

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
  • 6 replies
  • 1728 views
  • 0 likes
  • 2 in conversation