The SAS Output Delivery System and reporting techniques

How to - Give Colors From Another Colum Values

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

How to - Give Colors From Another Colum Values

Hello All,

 

Generally, when I need to add colors in my reports I benefit from this SAS Note(http://support.sas.com/kb/23/353.html ) and also I use Proc Format but this time it is litle bit complicated.

 

Please forgive me If I’m asking too basic question. But I try to give color from one variable to other variable. The Color column includes color values but I want to use the values on the Value column.

 

Please don’t ask me the logic of my code, this is just a sample code and I have similar data set as below and I need to show this values on Value column being a color.

 

Do you have an idea?

 

Data Have;
Length Target $ 32 Value 8 Color $ 32;
Infile Datalines Missover;
Input Target Value Color;
Datalines;
Variable1 0.5 Red
Variable2 0.3 Red
Variable3 0.7 Yellow
Variable4 0.4 Red
Variable5 0.6 Green
Variable6 0.8 Red
;
Run;

Proc Report Data=Have Nowd;

Column Target Value Color;

Define Target / Display ;
Define Value / Display ;
Define Color / Noprint;

Compute Before;
     If Color ="Red" Then Call Define(_c2_,"style","style={background=red}");
	 Else If Color="Yellow" Then Call Define(_c2_,"style","style={background=yellow}");
	 Else Call Define(_c2_,"style","style={background=green}");
Endcomp;
Run;

Thank you


Accepted Solutions
Solution
‎07-14-2016 03:50 PM
SAS Super FREQ
Posts: 8,866

Re: How to - Give Colors From Another Colum Values

Hi:

  When I run your code, this is what I get, which makes sense, because the color is being specified for the ROW, not for the cell (even though that's what you have in the data.

what_getting.png

 

But if you want to color each cell, for Variable 1 under 0.5, and have it be different from the cell for Variable 1 under 0.3 or 0.4, then you need to move the COLOR variable UNDER the ACROSS variable. Like this -- see the transition from Report 2, to Report 3, to Report 4:

what_need.png

 

Assuming, of course, that the final #4 is what you want, then the code to do all of this, based on your WORK.HAVE data is this code. Again, absolute column numbers are going to be necessary to accomplish this and you will need to know the number of items under VALUE in order to calculate the absolute column numbers to use.

 


title;
 
Proc Report Data=Have Nowd;
title '1) what you are getting now';
Column Color Target Value ;

Define Target / Group ;
Define Value / Across ;
Define Color / Noprint;

Compute value;
 Call Define(_col_,"style",cats("style={background=",color,"}"));
Endcomp;
Run;

Proc Report Data=Have Nowd;
title '2) what you need to get';
Column Target Value,(color n) ;

Define Target / Group ;
Define Color / group;
Define Value / Across ;
Run;

 
Proc Report Data=Have Nowd;
title '3) use absolute columns to set colors and prove that color coding works';
Column Target Value,(color n) ;

Define Target / Group ;
Define Color / group;
Define Value / Across ;

compute value;
length svar3 svar5 svar7 svar9 svar11 svar13 svar15 $100;
svar3 = catt('style={background=',_c2_,'}');
call define ('_c3_','style',svar3);

svar5 = catt('style={background=',_c4_,'}');
call define ('_c5_','style',svar5);

svar7 = catt('style={background=',_c6_,'}');
call define ('_c7_','style',svar7);

svar9 = catt('style={background=',_c8_,'}');
call define ('_c9_','style',svar9);

svar11 = catt('style={background=',_c10_,'}');
call define ('_c11_','style',svar11);

svar13 = catt('style={background=',_c12_,'}');
call define ('_c13_','style',svar13);

svar15 = catt('style={background=',_c14_,'}');
call define ('_c15_','style',svar15);

endcomp;
Run;

 Proc Report Data=Have Nowd;
title '4) now use noprint on Color';
Column ('Target' Target) Value,(color n) ;

Define Target / Group ' ';
Define Color / group noprint;
Define Value / Across ;
define n / ' ';

compute value;
length svar3 svar5 svar7 svar9 svar11 svar13 svar15 $100;
svar3 = catt('style={background=',_c2_,'}');
call define ('_c3_','style',svar3);

svar5 = catt('style={background=',_c4_,'}');
call define ('_c5_','style',svar5);

svar7 = catt('style={background=',_c6_,'}');
call define ('_c7_','style',svar7);

svar9 = catt('style={background=',_c8_,'}');
call define ('_c9_','style',svar9);

svar11 = catt('style={background=',_c10_,'}');
call define ('_c11_','style',svar11);

svar13 = catt('style={background=',_c12_,'}');
call define ('_c13_','style',svar13);

svar15 = catt('style={background=',_c14_,'}');
call define ('_c15_','style',svar15);

endcomp;
Run;
title;

  The only thing I do to keep the CALL DEFINE statement "clean" is that I use a temporary character variable called SVAR3, SVAR5, SVAR7 etc, to hold the whole style string that I want to apply, based on the color in the "neighbor" cell. Note how SVAR3 uses _C2_ value to create the entire STYLE= value. One of the cool things about the CALL DEFINE statement is that you can use an expression or a variable for the arguments to CALL DEFINE, so you don't have any quoting issues when you build the style string in a variable using the CATT function. Makes it easy to build the string, even if you have style attributes that are quoted.

 

Hope this points you in the direction you asked about.

 

cynthia

View solution in original post


All Replies
Super User
Posts: 10,028

Re: How to - Give Colors From Another Colum Values

Data Have;
Length Target $ 32 Value 8 Color $ 32;
Infile Datalines Missover;
Input Target Value Color;
Datalines;
Variable1 0.5 Red
Variable2 0.3 Red
Variable3 0.7 Yellow
Variable4 0.4 Red
Variable5 0.6 Green
Variable6 0.8 Red
;
Run;

Proc Report Data=Have Nowd;

Column Color Target Value ;

Define Target / Display ;
Define Value / Display ;
Define Color / Noprint;

Compute value;
 Call Define(_col_,"style",cats("style={background=",color,"}"));
Endcomp;
Run;
Trusted Advisor
Posts: 1,137

Re: How to - Give Colors From Another Colum Values

Please try to create a new format temp. from the existing dataset by cntlin option in proc format and use that format for displaying the row color.

 

Data Have;
Length Target $ 32 Value 8 Color $ 32;
Infile Datalines Missover;
Input Target Value Color;
Datalines;
Variable1 0.5 Red
Variable2 0.3 Red
Variable3 0.7 Yellow
Variable4 0.4 Red
Variable5 0.6 Green
Variable6 0.8 Red
;
Run;

/*create the format through data step*/
data fmt;
set have;
fmtname='temp';
start=value;
label=color;
type='N';
run;

proc format cntlin=fmt fmtlib;
run;

ods html body='temp.html';
Proc Report Data=Have out=test nowd;

Column Target Value Color;

Define Target / Display ;
Define Value / Display style={background=temp.};
Define Color / noprint;

Run;
ods html close;
Thanks,
Jag
Super Contributor
Posts: 395

Re: How to - Give Colors From Another Colum Values

Posted in reply to Jagadishkatam

Thank you very much @Ksharp and @Jagadishkatam,

 

Your both samples are very useful but I realized if I try to use Across option in PROC REPORT, the result seems different, how can I prevent this situation?

 

Data Have;
Length Target $ 32 Value 8 Color $ 32;
Infile Datalines Missover;
Input Target Value Color;
Datalines;
Variable1 0.5 Red
Variable2 0.3 Red
Variable3 0.7 Yellow
Variable4 0.4 Red
Variable5 0.6 Green
Variable6 0.8 Red
Variable1 0.5 Red
Variable2 0.4 Red
Variable3 0.8 Yellow
Variable4 0.3 Red
Variable5 0.5 Green
Variable6 0.9 Red
;
Run;

Proc Report Data=Have Nowd;

Column Color Target Value ;

Define Target / Group ;
Define Value / Across ;
Define Color / Noprint;

Compute value;
 Call Define(_col_,"style",cats("style={background=",color,"}"));
Endcomp;
Run;

Thank you

Trusted Advisor
Posts: 1,137

Re: How to - Give Colors From Another Colum Values

I am not sure using across is a correct option. Across will make the values variables to columns and it isn't what we are after. in that case, multiple columns will be created and if you want to make the changes to them then you have to use _c1_, _c2_ and so on.
Thanks,
Jag
Solution
‎07-14-2016 03:50 PM
SAS Super FREQ
Posts: 8,866

Re: How to - Give Colors From Another Colum Values

Hi:

  When I run your code, this is what I get, which makes sense, because the color is being specified for the ROW, not for the cell (even though that's what you have in the data.

what_getting.png

 

But if you want to color each cell, for Variable 1 under 0.5, and have it be different from the cell for Variable 1 under 0.3 or 0.4, then you need to move the COLOR variable UNDER the ACROSS variable. Like this -- see the transition from Report 2, to Report 3, to Report 4:

what_need.png

 

Assuming, of course, that the final #4 is what you want, then the code to do all of this, based on your WORK.HAVE data is this code. Again, absolute column numbers are going to be necessary to accomplish this and you will need to know the number of items under VALUE in order to calculate the absolute column numbers to use.

 


title;
 
Proc Report Data=Have Nowd;
title '1) what you are getting now';
Column Color Target Value ;

Define Target / Group ;
Define Value / Across ;
Define Color / Noprint;

Compute value;
 Call Define(_col_,"style",cats("style={background=",color,"}"));
Endcomp;
Run;

Proc Report Data=Have Nowd;
title '2) what you need to get';
Column Target Value,(color n) ;

Define Target / Group ;
Define Color / group;
Define Value / Across ;
Run;

 
Proc Report Data=Have Nowd;
title '3) use absolute columns to set colors and prove that color coding works';
Column Target Value,(color n) ;

Define Target / Group ;
Define Color / group;
Define Value / Across ;

compute value;
length svar3 svar5 svar7 svar9 svar11 svar13 svar15 $100;
svar3 = catt('style={background=',_c2_,'}');
call define ('_c3_','style',svar3);

svar5 = catt('style={background=',_c4_,'}');
call define ('_c5_','style',svar5);

svar7 = catt('style={background=',_c6_,'}');
call define ('_c7_','style',svar7);

svar9 = catt('style={background=',_c8_,'}');
call define ('_c9_','style',svar9);

svar11 = catt('style={background=',_c10_,'}');
call define ('_c11_','style',svar11);

svar13 = catt('style={background=',_c12_,'}');
call define ('_c13_','style',svar13);

svar15 = catt('style={background=',_c14_,'}');
call define ('_c15_','style',svar15);

endcomp;
Run;

 Proc Report Data=Have Nowd;
title '4) now use noprint on Color';
Column ('Target' Target) Value,(color n) ;

Define Target / Group ' ';
Define Color / group noprint;
Define Value / Across ;
define n / ' ';

compute value;
length svar3 svar5 svar7 svar9 svar11 svar13 svar15 $100;
svar3 = catt('style={background=',_c2_,'}');
call define ('_c3_','style',svar3);

svar5 = catt('style={background=',_c4_,'}');
call define ('_c5_','style',svar5);

svar7 = catt('style={background=',_c6_,'}');
call define ('_c7_','style',svar7);

svar9 = catt('style={background=',_c8_,'}');
call define ('_c9_','style',svar9);

svar11 = catt('style={background=',_c10_,'}');
call define ('_c11_','style',svar11);

svar13 = catt('style={background=',_c12_,'}');
call define ('_c13_','style',svar13);

svar15 = catt('style={background=',_c14_,'}');
call define ('_c15_','style',svar15);

endcomp;
Run;
title;

  The only thing I do to keep the CALL DEFINE statement "clean" is that I use a temporary character variable called SVAR3, SVAR5, SVAR7 etc, to hold the whole style string that I want to apply, based on the color in the "neighbor" cell. Note how SVAR3 uses _C2_ value to create the entire STYLE= value. One of the cool things about the CALL DEFINE statement is that you can use an expression or a variable for the arguments to CALL DEFINE, so you don't have any quoting issues when you build the style string in a variable using the CATT function. Makes it easy to build the string, even if you have style attributes that are quoted.

 

Hope this points you in the direction you asked about.

 

cynthia

Super Contributor
Posts: 395

Re: How to - Give Colors From Another Colum Values

Posted in reply to Cynthia_sas

@Cynthia_sas,

 

Thank you very much for your detailed information and sorry for my late response.

 

Actually,  I want to change my sample data set, what if my data set is as below, how can I give the color to the Value column. I want to do with PROC FORMAT, because the number of columns can be change. Of course, I want to do with PROC FORMAT if it is possible.

 

At the following example, I tried to use @Jagadishkatam example but I got an error like ->ERROR: For format TEMP, this range is repeated, or values overlap: 0.5-0.5. I didn't understand the reason

But the "Results" tab show what I want, I just need to add colors into the "Value" column by using "Color" column values.

 

If PROC FORMAT is not proper for my purpose, I'm okay to listen your methods.

 

Thank you very much

 

Data Have;
Length Target $ 32 Value 8 Value2 8 Color $ 32 Dataset $ 32;
Infile Datalines Missover;
Input Target Value Value2 Color Dataset;
Datalines;
Variable1 0.5 0.4 Red Data1
Variable2 0.3 0.2 Red Data1
Variable3 0.7 0.75 Yellow Data1
Variable1 0.4 0.45 Red Data2
Variable2 0.8 0.85 Green Data2
Variable3 0.5 0.6 Red Data2
Variable1 0.4 0.3 Red Data3
Variable2 0.9 0.8 Green Data3
Variable3 0.3 0.25 Red Data3
;
Run;

data fmt;
set have;
fmtname='temp';
start=value;
label=color;
type='N';
run;

proc format cntlin=fmt fmtlib;
run;

Proc Report Data=Have out=test nowd;

Column Target Dataset,(Value Value2 );

Define Target / Group ;
Define Dataset / Across style={background=temp.};
Define Value / Analysis;
Define Value2 / Analysis;

Run;

Thank you,

 

SAS Super FREQ
Posts: 8,866

Re: How to - Give Colors From Another Colum Values

Hi: This would be nearly impossible to do with PROC FORMAT since you have ACROSS for the DATASET variable. With both Value and Value2 under the DATASET variable, it will be impossible for PROC FORMAT to know which column for which DATASET. That is why PROC REPORT has absolute column numbers for across items.

Did you look at my previously posted example? The only difference with VALUE and VALUE2 is that the column numbers you used in the CALL DEFINE would change.
And, you CANNOT apply the format to DATASET, because DATASET is a character variable and your format is a numeric format meant to be applied to either VALUE or VALUE2.

What is the problem with using Absolute Column Numbers???

cynthia
Super Contributor
Posts: 395

Re: How to - Give Colors From Another Colum Values

Posted in reply to Cynthia_sas

Hello @Cynthia_sas,

 

I will try to use your sample but even though your sample  works fine, errors are created on the log? What can be the reason? Maybe I can miss something?

 

Thank you

Super Contributor
Posts: 395

Re: How to - Give Colors From Another Colum Values

I'm little bit confuses, I tried to do by using your method but your method can be advance for me, I'm not sure.

 

I just want to see following image, but I didn't handle it Smiley Sad

 

Proc Report Data=Have Nowd;
title " ";
Column Target Color Dataset ,(Value/*,(Color)*/) ;

Define Target / Group ;
Define Color / Group Noprint ;
/*Define N / " " Group  ;*/
Define Dataset / Across ;
Define Value / Analysis /*Across*/;

Compute Value;
Length SVar3 SVar5 SVar7 $100;
SVar3 = Catt('style={background=',_c2_,'}');
Call Define ('_c3_','style',SVar3);

SVar5 = catt('style={background=',_c4_,'}');
Call Define ('_c5_','style',SVar5);

SVar7 = catt('style={background=',_c6_,'}');
Call Define ('_c7_','style',SVar7);

EndComp;
Run;
title;

Desired.png

SAS Super FREQ
Posts: 8,866

Re: How to - Give Colors From Another Colum Values

Hi:

  So in my code, I had the color variable INSIDE the parentheses for the ACROSS, like this:

Column Target Value,(color n) ;

and in your code, you have color OUTSIDE the parentheses like this:

Column Target Color Dataset ,(Value ) 

why did you do that? If my example produced the output you wanted, then, as I explained in my example, if you want a different COLOR for every possible value of DATASET/TARGET combination then COLOR has to be UNDER the DATASET, not OUTSIDE.

 

With COLOR outside of the ACROSS, it does not get an absolute number. So it is just COLOR NOT _C2_. So when I ran your ORIGINAL code, you had the unique VALUE numbers as column headers and you had the counts in each cell. The output you are posting now looks completely different, with the VALUE numbers INSIDE each cell. This is an entirely different report. The way I see this NEW report, it appears that value under 0.5 should be READ and any value above 0.8 should be GREEN without regard to the value for DATASET. Is this correct? If this is what you want, this is not the type of output you originally posted.

 

cynthia

SAS Super FREQ
Posts: 8,866

Re: How to - Give Colors From Another Colum Values

Posted in reply to Cynthia_sas

Hi:

  OK. I'll admit there have been too many examples and now I am confused by what you really want or need. So I'll take another chance that this is what you want. With 2 variables under the ACROSS variable, you CAN have user defined formats. For purposes of illustration, I made some arbitrary values and made a format with the hardcoded values and colors.highlighting_with_format_for_ACROSS.png

 

 My format works without reference to any absolute column numbers or need for CALL DEFINE because every 0.5 for the VALUE variable will get colored RED without regard to whether the DATASET variable is DATA1, DATA2 or DATA3. If this is OK, then you can have a user-defined format create the background color as shown in my code. Note that I completely removed the color value from your sample data so as not to confuse the issue.

 

However, and here is the huge, big warning, IF on the first row (for Variable 1) you want the 0.4 for DATA3 to be colored differently than the 0.4 for DATA2, then you will need to go to a more complicated model for the color coding and the format method will NOT work. When PROC REPORT is putting down the variables under the ACROSS, it has NO visibility of which cell belongs to DATA1, DATA2 or DATA3. It only knows that the VALUE for DATA1 is _c2_ in the above scenario and that VALUE2 for DATA1 is _c3_ and that value for DATA2 is _c5_ and that value2 for DATA2 is _c6_.

 

I am sorry that so many different versions of the data have been posted and so many different PROC REPORT examples, I have gotten confused about what you actually want and before you start going down the road of making data that has color variables, let's see if you can get what you want based on the actual data you have and the actual report you want. 

 

cynthia

Super Contributor
Posts: 395

Re: How to - Give Colors From Another Colum Values

Posted in reply to Cynthia_sas

@Cynthia_sas,

 

Sorry for tired you out, the following code meets my desired output.

 

I investigated your previous code more deeply and I resolved the my desired output. The output seems proper, what do ypu think, can I use this code for my aim?

 

Proc Report Data=Have Out=Want2 Nowd;
title " ";
Column Target  Dataset ,(Color Value) ;

Define Target / Group  Style(Column)=Header;
Define Color / Group Noprint ;
Define Dataset / Across ;
Define Value / Analysis ;


Compute Value;
Length SVar3 SVar5 SVar7 $100;
SVar3 = Catt('style={background=',_c2_,'}');
Call Define ('_c3_','style',SVar3);

SVar5 = catt('style={background=',_c4_,'}');
Call Define ('_c5_','style',SVar5);

SVar7 = catt('style={background=',_c6_,'}');
Call Define ('_c7_','style',SVar7);

EndComp;
Run;
title;

Thank you

SAS Super FREQ
Posts: 8,866

Re: How to - Give Colors From Another Colum Values

Hi: I wasn't tired, only confused. With COLOR under DATASET (the ACROSS item) in the table structure, then it seems to me that your code is correct for what you want.

cynthia
Super Contributor
Posts: 395

Re: How to - Give Colors From Another Colum Values

Posted in reply to Cynthia_sas

Hello @Cynthia_sas,

 

Thank you very much for your detailed answers Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 689 views
  • 8 likes
  • 4 in conversation