topic Re: proc report : Excel ODS Color code Display based on the String contains in SAS Programming

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

SASuserlot — Thu, 08 Oct 2020 00:28:39 GMT

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.

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

jimbarbour — Thu, 08 Oct 2020 02:06:25 GMT

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

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

SASuserlot — Thu, 08 Oct 2020 02:23:51 GMT

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;

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

jimbarbour — Thu, 08 Oct 2020 02:32:53 GMT

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

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

SASuserlot — Thu, 08 Oct 2020 02:35:34 GMT

 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;

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

SASuserlot — Thu, 08 Oct 2020 02:40:35 GMT

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

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

jimbarbour — Thu, 08 Oct 2020 02:42:24 GMT

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

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

SASuserlot — Thu, 08 Oct 2020 03:00:04 GMT

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

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

SASuserlot — Thu, 08 Oct 2020 03:07:04 GMT

tried with correct spelling , no change.

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

ballardw — Thu, 08 Oct 2020 04:25:37 GMT

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.

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

SASuserlot — Thu, 08 Oct 2020 12:46:29 GMT

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

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

Cynthia_sas — Thu, 08 Oct 2020 13:38:06 GMT

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

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

SASuserlot — Thu, 08 Oct 2020 13:41:56 GMT

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.

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

SASuserlot — Thu, 08 Oct 2020 13:53:28 GMT

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.

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

ballardw — Thu, 08 Oct 2020 14:26:02 GMT

@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-data-AKA-generate/ta-p/258712 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.

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

SASuserlot — Thu, 08 Oct 2020 14:35:37 GMT

I am not expert, but not sure about the format you mentioned works for what I am looking. I am looking for if any variable that has the string that contains a word "Missing" need highlight that cell in output. according to your format Variable has to have the only word that mentioned in formats. where as I am looking for finding the word Missing in string then alert with color code. Let me know if My question make sense. also is it any way we can create a separate variable flag based on this logic and work around!

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

SASuserlot — Thu, 08 Oct 2020 14:46:03 GMT

I just started this community. Thanks for suggestion, I will learn to post the data, Thanks for the instruction, will learn a lot from you guys

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

Cynthia_sas — Thu, 08 Oct 2020 15:45:06 GMT

Hi:

My example exactly mimics your first posting, as shown below:

Your second posting example has these issues that I have a hard time figuring out because there's no context for the data or the desired output:

1) data shown is screen shot, data structure is not explained and can't be used with only partial code provided

2) data is not explained. Is it a CSV file? Is it a SAS dataset? Why are there ODS ESCAPECHAR strings in the data?

3) there is no color coding in the example that is supposed to be Excel output. The 3rd picture doesn't look like Excel output. There are not any cell boundaries and no column headers.

It is possible to do color coding with ODS EXCEL. I'm not sure that if you have multiple strings in a cell that you can do 2 different backgrounds in one cell but you CAN have 2 different font colors, as shown below:

One thing I like to do is to remove SAS from the picture and see whether something is possible in native Excel. So for example, in this worksheet that I typed into Excel, I can change the entire background for one cell, no matter how many text strings are in the cell:

However, look at the cell B3, where I have highlighted just the word "number" in the cell, the button to change the background becomes grayed out and there's not a way to change the background color of just one piece of the string. I can still change the font color of the highlighted word, as shown below:

So if Excel won't let you change the background of just one piece of a text string, SAS won't be able to do it either.

Cynthia

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

SASuserlot — Thu, 08 Oct 2020 16:04:34 GMT

Yes I totally agree with you. I am not looking to achieve both. I am fine with if it just highlights the cells with color that contains the word "Missing". I am sorry if Initial post caused you any confusion. I am posting another, let me know if it clears to you.

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

Cynthia_sas — Thu, 08 Oct 2020 19:29:06 GMT

Hi:
What you posted in your top picture, you say is how the data looks in the SAS dataset -- but you show it as row 21 -- that looks like a screen shot from Excel for what your data resembles. It is impossible to understand your data structure from just one row and one cell. Several examples of how to write a data step program to make fake or dummy data into a SAS dataset for testing have been posted. It is nearly impossible to understand what your data looks like from the first picture which is essentially row 21 and just one cell on row 21.
Your second screen shot shows the words [Missing], Control, Food Effect and Treatment, and your comment says that this is how you want your data displayed in Excel. But again it looks like you are just showing 1 cell in column C in Excel. You don't show columns A and B. However your first screen shot looks like it is showing row 21, Column A, so did your data suddenly shift to Column C when you did your report? You had many more variables in your PROC REPORT code that you posted but these snippets show none of those variables or variable names and you haven't posted more than 1 or 2 cells. And in your last picture it shows continuous and continuous, content, etc. In other words, the first and second screen shots do not agree at all with what you say your data is and nothing looks like your PROC REPORT code will work with what you've posted.
Cynthia