BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

14 REPLIES 14
Ksharp
Super User
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;
Jagadishkatam
Amethyst | Level 16

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
turcay
Lapis Lazuli | Level 10

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

Jagadishkatam
Amethyst | Level 16
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
Cynthia_sas
SAS Super FREQ

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

turcay
Lapis Lazuli | Level 10

@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,

 

Cynthia_sas
SAS Super FREQ
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
turcay
Lapis Lazuli | Level 10

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

turcay
Lapis Lazuli | Level 10

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 😞

 

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

Cynthia_sas
SAS Super FREQ

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

Cynthia_sas
SAS Super FREQ

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

turcay
Lapis Lazuli | Level 10

@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

Cynthia_sas
SAS Super FREQ
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
turcay
Lapis Lazuli | Level 10

Hello @Cynthia_sas,

 

Thank you very much for your detailed answers 🙂

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
  • 14 replies
  • 2043 views
  • 8 likes
  • 4 in conversation