topic Characterizing rows with colurs in SAS Programming

Characterizing rows with colurs

Bruce123 — Thu, 26 Sep 2013 13:10:02 GMT

Hello

I wish to write a code that evaluates the value of three variables.

I want to do thus that if each of the three variable's value is negaitv, than all valuescolurs turn red ( this is to be done when I am doing a proc report if possible).

If two of the variable's value are negativ and one is positive, then all the values turn yellow.

If two of the varible's value are positiv and one is negative, then all the values turn blue

And if all the variable's value are positive, then they all turn green.

For example, if you look at the attached file I would want all the values in that row to turn blue ( you can just ignore the variable performance 2010, so there is only 3 variables).

Is this possible?

Re: Characterizing rows with colurs

LeRoyBessler — Thu, 26 Sep 2013 16:16:06 GMT

This looks like an interesting programming problem,

but I don't have time to deal with it now.

What I CAN do is mention that

the inability to distinguish red and green is commonest form of color blindness,

and you might wish to take that into account in your color-coding rule.

To me, this looks like a two-step process.

One pass to determine the color for each row.

Then a report-writer step of some sort to color part of the row content based on row number.

Yes, I'm stating the obvious, and you need the code.

I will be interested in seeing the solution.

Good Luck.

Re: Characterizing rows with colurs

LeRoyBessler — Fri, 04 Oct 2013 00:14:31 GMT

Well, a few days after the original question above from Bruce123, I got involved in responding to a series of color-coding questions by another SAS user (Harry) in postings at the SAS Analytics and BI LinkedIn group. To see that dialogue, if interested, go to

http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=277345018&gid=130238&commentID=167395902&trk=view_disc&fromEmail=&ut=1HO7sBtlf0aBY1

One of Harry's questions there was analogous to the question from Bruce123 here.

That answer to both questions, from Bruce123 and Harry, is in the second example in SAS Note http://support.sas.com/kb/23/353.html

Re: Characterizing rows with colurs

SteveNZ — Fri, 04 Oct 2013 01:00:57 GMT

You could do this:

data want ;

length colour $6 ;

set have ;

if var1 < 0 then varind1 = -1 ;

else varind1 = 1 ;

if var2 < 0 then varind2 = -1 ;

else varind1 = 1 ;

if var3 < 0 then varind3 = -1 ;

else varind1 = 1 ;

varscore = sum(varind1,varind2,varind3) ;

if varscore = -3 then colour = 'Red' ;

else if varscore = -1 then colour = 'Yellow' ;

else if varscore = 1 then colour = 'Blue' ;

else colour = 'Green' ;

run ;

Then use the colours in a format to traffic light the proc report.

Re: Characterizing rows with colurs

Bruce123 — Mon, 07 Oct 2013 15:17:34 GMT

Hello

Thank you for the link. It seems to be what I was looking for.

However, I have encountered a new problem, and I am hoping it is possible for some help.

As I see it you can only use the "compute" function on one variable at a time. As I see it, I have to use a "compute" function on 3 variables, but I am not sure if this is possible. I could do a "compute" three times separtely, but that I beleive will be ineffiecent, since if do one for each add a colour to at row then they will overwrite eachother, and I cannot add the colour on columns because I need it to be on the row.

I am looking for, if possible, to compute to three variables at once. Below you can see my code to see what I am trying to say. You should just look at the "compute-part" of the code. The rest of the code is fine. I can see that the compute-part is wrong, but my idea is to in that kind of way, if possible.

PROC REPORT DATA = Have 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.';

WHERE mv >= 0.5 AND ParentNodeName in ('Aktier', 'Obligationer', 'Ejendomme');

COLUMNS OBS ParentNodeName ModelPortfolioName Performance_2010 Performance_2011 Performance_2012 Performance_2013;

DEFINE OBS / GROUP NOPRINT;

DEFINE ParentNodeName / 'Type' GROUP;

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

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

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

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

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

BREAK AFTER ParentNodeName / PAGE;

Compute Performance_2013; Performance_2012; Performance_2011;

if performance_2013 <=0 and Performance_2011 <=0 and Performance_2012 <=0 Then Call Define (_row_,"style","style={background=blue}");

Else if performance_2013 >0 and Performance_2011 <0 and Performance_2012 <=0 Then Call Define (_row_,"style","style={background=yellow}");

Endcomp;

RUN;

Is this possible?