Trouble trafic lighting a column based on cells value

Reply
Contributor
Posts: 47

Trouble trafic lighting a column based on cells value

Hello

I am looking to trafic light the cells in a column based on its value.

If the value is 0 the cells in the specific columns with that value turns blue, if the value is 3 then the cells in the specific column with that value turns green and so on.

But for some reason Sas makes the whole columns the same colour even though there are different values in the column.

Below is my code. I have copied the whole proc report, so you can see the whole code-part, but it is basically only the "compute" part that needs looking.

RUN;

PROC REPORT DATA = Indikator HEADLINE NOWINDOWS SPLIT='*' Contents='' /* Removes node entirely */ spanrows /* Θn stor celle pr. gruppe vμrdi */

%ReportStyles

STYLE(column) = [background=white cellwidth=75pt]

STYLE(report)= [font_size=0.5 font_face=Times]; /* Makro med report indstillinger */

TITLE FONT=Times HEIGHT=4 Color=Black  "Performance for de 3 seneste σr (pct. og mio. kr.) pσ forvaltere pr. %SYSFUNC(putn(&ReportDate,DDMMYY10.))";

FOOTNOTE FONT=Times HEIGHT=4 Color=Black 'Performance mσlt ift. forvalter benchmark.';

COLUMNS Model Perf10 Perf11 Perf12 Perf13 Indikator;

DEFINE Model / 'Forvalter' GROUP STYLE(column) = [cellwidth=/*150pt*/130pt] ORDER=DATA;;

DEFINE Perf10 / 'Performance 2010'  FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

DEFINE Perf11 / 'Performance 2011'  FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

DEFINE Perf12 / ' Performance 2012' FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

DEFINE Perf13 / 'Performance TD'   FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt] style(column) = {font_weight=Bold foreground=cback.};

DEFINE Indikator / 'Indikator' FORMAT=COMMAX20.1 STYLE(column) = [cellwidth=/*50pt*/40pt];

Compute Indikator;

if indikator = 0 then Call Define (_col_,"style","style={background=blue}");

else if 1 then call define (_col_,"style","style={background=Green}");

Else if 3 then call define (_col_,"style","style={background=yellow}");

Else if -1 then call define (_col_,"style","style={background=pink}");

Else call define (_col_,"style","style={background=red}");

endcomp;

RUN;

I am not at work at the moment, so I cannot run the code, but could the problem be that I am missing an "=" inclusion in the compute part? I have to prepare this for tomorrow so I would like to be sure that the code is ready.

Can someone help?

All help will be greatly appreciated.

Thank you

SAS Super FREQ
Posts: 8,866

Re: Trouble trafic lighting a column based on cells value


Hi:

  I can think of 2 possible issues. STYLE= overrides onlly work with destinations that support style changes. Since you did NOT show your ODS statements, it is hard to understand whether you are using the LISTING destination (no style) or another (RTF, PDF, HTML, etc) that does support style.

  The other possibility is that you did not say whether you are getting error messages in the SAS log. I would expect to see some if INDICATOR is numeric. You have not specified any USAGE (GROUP, ORDER, DISPLAY, ANALYSIS) for your variables, so PROC REPORT will take all the defaults. If INDICATOR is a CHARACTER variable, with a default usage of DISPLAY, then your COMPUTE block is correct; however, if your INDICATOR variable is a NUMERIC variable (which I suspect), then your COMPUTE block is incorrect. PROC REPORT expects a "compound reference" of

var_name.stat_name

in a COMPUTE block. In your example, this would be INDICATOR.SUM, (not the simple INDICATOR variable name), since the default usage for a numeric variable is: ANALYSIS with a statistic of SUM.

  Are you seeing error messages in the log with your code?

cynthia

Contributor
Posts: 47

Re: Trouble trafic lighting a column based on cells value

Posted in reply to Cynthia_sas

Hello

Thank you for your reply

Yes, the variable "indicator" is a numeric variable

Ok, I was not aware that I had to writ "Indicator.Sum". I tried to following the guidelines from here http://support.sas.com/kb/23/353.html and using a mix of example 1 and 2, but I guess these are for the default usage.

I am using a ODS PDF. The ODS statement was written by a former colleague, but I am quite certain that the ODS supports style. I do not have this part of the code on me at the moment, but I can show it later if needed.  

No, I am not seeing any errors in the log.

But I will delete the style= and replace them so they no override each other.

But if I understand you correctly, then if I change the style= and in the compute part and write the following, then it will work?

Compute Indikator.Sum;

if indikator = 0 then Call Define (_col_,"style","style={background=blue}");

I am quite new to Sas-progrommering, so I apologize if I have not giving you the needed information. But I would like to say that I really appreciate you taking the time to help. 

Ask a Question
Discussion stats
  • 2 replies
  • 182 views
  • 0 likes
  • 2 in conversation