proc report : Excel ODS Color code Display based on the String contains

[SASuserlot]

I output my sas data to ods Excel format, I have a requirement that if the variable contains a certain string, I need to do some kind of color coding ( background color, or change the color of the font) to display in excel file. I do have the idea of doing it in compute block for numeric variable > or < conditions. Never done strings, please suggest some ideas, Example coding in the compute block of proc report or directing to sources also greatly appreciated. Thanks. I am attaching the sample of how I want Variable 1, how it is there in my data. variable2 is how I want to show in excel output, If the string contains "continuous" I need to do background color yellow and/or font in red.

[jimbarbour]

In a Proc Report, try the following COMPUTE block:

COMPUTE	Variable2;
	IF	INDEX(UPCASE(Variable2), 'CONTINUOUS')	THEN
		CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUD=YELLOW]");
ENDCOMP; 

Jim

[SASuserlot]

tried no luck, but its not throwing error either. attaching code, second pic is the how variable look in dataset, third excel output

 PROC REPORT data=sdsp5 nowindows
style(report)=[font=("Times New Roman", 11pt) protectspecialchars=on]
style(header)=[font=("Times New Roman", 11pt, bold) just=center protectspecialchars=on]
style(column)=[font=("Times New Roman", 11pt) protectspecialchars=on];  
.............
..............
...............  
      DEFINE Study_Design/ "" display style=[just=c vjust=t cellwidth=8.882cm tagattr='type:String format:Text'];
      DEFINE Study_status /" " display style=[just=c vjust=t cellwidth=2.718cm tagattr='type:String format:Text'];
      DEFINE Study_start_date /"" display style=[just=c vjust=t cellwidth=2.818cm tagattr='type:String format:Text'];
      DEFINE Exchange_standards /"" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
      DEFINE terminology_standards /"" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
%end;
	compute before hline/ style=[font=("Times New Roman", 11pt, bold) ];
	 line @140 hline $100.;
	 if index((Study_design),"[Missing]") then call define(_col_, "style","style=[Background=Yellow]");
	endcomp;
   RUN;

[jimbarbour]

Is that your entire Proc Report definition?  I am used to seeing COLUMN definitions.

And I'm used to seeing COMPUTE Study_Design if I'm doing a call define for the column Study_Design.

Jim

[SASuserlot]

 PROC REPORT data=sdsp5 nowindows
       style(report)=[font=("Times New Roman", 11pt) protectspecialchars=on]
      style(header)=[font=("Times New Roman", 11pt, bold) just=center protectspecialchars=on]
      style(column)=[font=("Times New Roman", 11pt) protectspecialchars=on];
/*	  by ph ;*/
	  where &ph;
      COLUMN ph Hline Study_Identifier brief_title Study_Design Study_status Study_start_date
	   		 Exchange_standards terminology_standards;
      DEFINE ph/ " " group noprint style=[just=c vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
      DEFINE Hline/ " " group noprint style=[font=("Times New Roman",11pt)just=c vjust=t vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
 %if &phx=1 %then %do;
      DEFINE Study_Identifier/"Study Identifier" display style=[just=l vjust=t cellwidth=2cm tagattr='type:String format:Text'];
      DEFINE brief_title/ "Brief Title" display style=[just=c vjust=t cellwidth=3.882cm tagattr='type:String format:Text'];
      DEFINE Study_Design/ "Study Design" display style=[just=c vjust=t cellwidth=5.882cm tagattr='type:String format:Text'];
      DEFINE Study_status /" Study Status" display style=[just=c vjust=t cellwidth=2.718cm tagattr='type:String format:Text'];
      DEFINE Study_start_date /"Study Start Date" display style=[just=c vjust=t cellwidth=2.818cm tagattr='type:String format:Text'];
      DEFINE Exchange_standards /"Exchange standards" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
      DEFINE terminology_standards /"Terminology Standards" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
  %end;

%else %do;
      DEFINE Hline/ " " group noprint style=[font=("Times New Roman",11pt)just=c vjust=t cellwidth=2.745cm tagattr='type:String format:Text'];
      DEFINE Study_Identifier/"" display style=[just=l vjust=t cellwidth=2cm tagattr='type:String format:Text'];
      DEFINE brief_title/ "" display style=[just=c vjust=t cellwidth=3.882cm tagattr='type:String format:Text'];
      DEFINE Study_Design/ "" display style=[just=c vjust=t cellwidth=8.882cm tagattr='type:String format:Text'];
      DEFINE Study_status /" " display style=[just=c vjust=t cellwidth=2.718cm tagattr='type:String format:Text'];
      DEFINE Study_start_date /"" display style=[just=c vjust=t cellwidth=2.818cm tagattr='type:String format:Text'];
      DEFINE Exchange_standards /"" display style=[just=c vjust=t cellwidth=3.212cm tagattr='type:String format:Text'];
      DEFINE terminology_standards /"" display style=[just=c vjust=t cellwidth=4.643cm tagattr='type:String format:Text'];
%end;
	compute before hline/ style=[font=("Times New Roman", 11pt, bold) ];
	 line @140 hline $100.;
	IF	INDEX(UPCASE(Study_design), "[MISSING]")	THEN
		CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUD=YELLOW]");
	endcomp;
   RUN;

[SASuserlot]

I created as macro  first part  defines displays the headings and variables of one group later part only display the variables of different groups.

[jimbarbour]

Background appears to be misspelled.  We should correct that first.

	compute before hline/ style=[font=("Times New Roman", 11pt, bold) ];
	 line @140 hline $100.;
	IF	INDEX(UPCASE(Study_design), "[MISSING]")	THEN
		CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUD=YELLOW]");
	endcomp;

And what is this code in the COMPUTE block doing?

	 line @140 hline $100.;

Jim

[SASuserlot]

line is creating the header as hline variable in the excel sheet at row 4 for particular group and other places and and its alignment and row 3 is the overall heading for all studies

[SASuserlot]

tried with correct spelling , no change.

[ballardw]

For testing remove any macro elements and use a reduced data set, or just plain new data with just one or two variables.

get the color coding to work. Then build back to the bigger picture.

If the question involves specific VALUES of a variable one way is to consider a format.

Proc format;
value $mytextcolor
"Home" = 'pink'
"Street" = 'yellow'
;

data example;
   input row word $;
datalines;
1  Sometext
2  Home
3  .
4  Street
;

proc report data=example;
   columns row word;
   define word/display style={background=$mytextcolor.};
run;

You could use any of the SAS color naming conventions in the format.

And if your values are actually missing, instead placing a text value placed in them you could use two formats.

Proc format;
value $mytextcolor
"Home" = 'pink'
"Street" = 'yellow'
"Missing"     = 'red'
;
value $mytextvalue
" " = "Missing"
;
run;
data example;
   input row word $;
datalines;
1  Sometext
2  Home
3  .
4  Street
;

proc report data=example;
   columns row word;
   define word/display style={background=$mytextcolor.}
               format=$mytextvalue.
   ;
   
run;

The background color format uses the formatted value of the variable to determine the color.

You may have to share the ODS EXCEL destination code at some point.

[SASuserlot]

your suggestion helpful but I still want to display the "Missing" my shells and color code when the shell work containing  this word.

[Cynthia_sas]

Hi:

  The point is that if the sample program works for you to produce color coding as per the program, then you should be able to make color coding work with your data. But it is your data that I have a question about:

Is this really your data? It looks like your data has ODS ESCAPECHAR control strings (like ^n or line feed) inside the data value. In your screen shot are you showing 1 column's value or is Missing in one column and ^nControl in another column? What about the second line? Is that one column or 3 or 4 columns? Your data does not make sense. However IF your text value is one column's value and IF you only want the word MISSING to be highlighted then you need to work with inline formatting.

  But without some real sample data and a program that is not already macro-ized, you are only providing part of the picture. We'd also need your ODS EXCEL statements and to see ALL of the ODS EXCEL sub-options you're using. Your original post showed one value highlighted and in your data, I do not see the value you want highlighted.

  Based on your very first posting, I was able to generate this:

using this code:

data fakedata;
  length id $2 variable1 $20 variable2 $20;
  infile datalines dlm=',' dsd;
  input id $ variable1 $ variable2 $;
datalines;
1a,continuous, continuous
2b,constant, continuous
3c,continuous, continuous
4d,viscous, viscous
5e,not now, not now
6f,constant, constant
7g,but not true, but not true
;
run;

proc format;
  value $bkfmt 'continuous' = 'yellow'
              other = 'white';
  value $forefmt 'continuous' = 'red'
                 other = 'black';
run;

ods excel file='c:\temp\usefmt_color.xlsx';
proc report data=fakedata;
  column id variable1 variable2;
  define id / order;
  define variable1 / display;
  define variable2 / display 
         style(column)={background=$bkfmt. color=$forefmt.};
run;
ods excel close;

  You now have 2 programs that work to produce color coding. If your program is not working as you expect, then either post some simplified code and some real data to the forums so others can understand the exact issue and then please explain what you want.

  Thanks for clarifying and providing more context and your exact data.

Cynthia

[SASuserlot]

First I want to say thank you for looking into my post. I am fan of your ans and post. I still follow your post.

1. I provided my  complete proc report section, I providing the excel options used.

2.ods ESCAPECHAR = '^';
ods excel file='C:\Users\xxxu\Desktop\New folder\Guy\exa.xlsx' options(sheet_name='leveling'
 sheet_interval='none')

2  The screen shot is the data,   I used the^n so that every word after ^n display in next line in excel output

3. The string you seen in screen shot , is one row string, may be its came in the picture like that because I did not widen the width. they string is just made by concatenating the words, separated by "comma"

4. I am  not looking for the word to highlight. I am looking if any cell in excel or dataset contains the "missing" , I want to color that. its just to alert me in excel sheet some thing missing in that particular cell. I hope I given information you looking for. Thanks again following the post.

[SASuserlot]

I really appreciate you taking take time and created a data set and show me the example. However  If you see  you fakedata set where you created dataset from excel, the second row is a string that contains the word "continuous constant" so it has to be highlighted or alert with some color. Where as in your dataset you created second row also continuous. so my question is how  create the format if you looking for a word in a string. 

[ballardw]

---

@SASuserlot wrote:

your suggestion helpful but I still want to display the "Missing" my shells and color code when the shell work containing  this word.

---

So which part does my example not cover???

You have not provided useable  data. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

And there is no way we can code against a picture that does not contain the actual values of variables in a data set.