How to highlight partial string within a cell PROC REPORT CALL DEFINE

[purpleclothlady]

Hi all:

just to see if there is a way in proc report call define to highlight partial string within a cell.

eg. id=1, highlight only Time part of variable -"Date/Time"

This code is not working.

compute flagdt;
if flagdt = "time" then do;
call define("compress(substr(dt,12,8))",'style','style = [background = yellow]');
call define("compress(substr(bdt,12,8))", 'style','style = [background = yellow]');
end;

else if flagdt = "date_time" then do;
call define("dt",'style','style = [background =light purple]');
call define("bdt", 'style', 'style = [background =light purple]');
end;
endcomp;

thanks 

Purple

[Cynthia_sas]

Hi:

  For a question like this we need to see ALL of your code. Here's why: PROC REPORT processes each report row, one row at a time, building the report row from LEFT to RIGHT. Your COMPUTE block is for the FLAGDT variable. Whether the COMPUTE block will work, depends on where the FLAGDT variable is placed on the COLUMN statement because that will impact the COMPUTE block based on the position of the variables DT and BDT on the COLUMN statment, since those are the variables you are trying to change based on the value of FLAGDT. So you have 3 variables and we need to see how you've listed them from left to right on the COLUMN statement because that has a direct impact on whether the CALL DEFINE will work or not.

  Consider these examples using SASHELP.CLASS:

  I did not do anything about answering your original question about highlighting the content in part of the cell because you need to fix the COMPUTE block first to make sure the variables you want to impact are being referenced in the right COMPUTE block. Note how in the #1 example, the COMPUTE block for AGE is trying to change the style for HEIGHT. This does NOT work. Working from left to right, at the point in time when AGE is placed on the report row and the COMPUTE block for AGE executes, the cell for HEIGHT has not yet been placed on the report row, so there is nothing for the CALL DEFINE to impact. This is a timing issue and inherent in the way that PROC REPORT builds the report row working from left to right and executes the COMPUTE blocks. If PROC REPORT has visibility of AGE on the report row but does not have visibility of HEIGHT (because it has not yet been placed on the report row), then the CALL DEFINE statement for style will not work.

  On the other hand, in the #2 example, the COMPUTE block is for HEIGHT and since AGE was placed on the report row before HEIGHT, by the time the COMPUTE block for HEIGHT is executing, the value for AGE has been placed on the report row and the value for HEIGHT is now on the report row, so the IF statement and the CALL DEFINE work correctly.

  The other reason I did not address your issue about changing the highlighting in the cell for part of the value is that it makes a difference whether the variable is character or numeric. It matters whether you have declared an ODS ESCAPECHAR so you can do in-line formatting. Here's an example of in-line formatting for character variables. Basically, using SASHELP.SHOES, I want the "Europe" in Eastern Europe and Western Europe to appear in an orange font for REGION value. For PRODUCT value, I want the word "Casual" in purple and I want the word "Dress" in green. Note how I had to make a computed item and needed to insert the in-line formatting ESCAPECHAR style override into the value.

But without data or your whole program, there is too much unknown to provide more specific help.

Cynthia

[purpleclothlady]

hi @Cynthia_sas :

Thanks so much for the details of diagnosis.

Example1: 

here is the code. It worked before. I didn't say it clearly :

I am asking if there is a way to highlight partial cell (NOT highlight partial of a text string)  

ods excel options ;
proc report data = rpt nowd split='*' missing style(header)=[just=center];

/*columns variable order to correctly display compute block*/
column subject dt bdt flagdt;

define subject / "ID" order=data ;
define dt / "Date/Time"  ;
define bdt / "Date/Time"  ;
define flagdt / display noprint;

compute flagdt;
if flagdt = "ti" then do;
call define("compress(substr(dt,12,8))",'style','style = [background = yellow]');
call define("compress(substr(bdt,12,8))", 'style','style = [background = yellow]');
end;
else if flagdt = "dt_ti" then do;
call define("dt",'style','style = [background = purple]');
call define("bdt", 'style', 'style = [background = purple]');
end;
endcomp;
run;

Example2: escapchar =' ^'    

I  tested the code, somehow it didn't work ,here is the code: please advise. thanks again. 

Purple

proc sort data=sashelp.shoes out=newshoes;
where region contains "Europe" and sales >100000 and 
(product contains "Casual" or product contains "Dress");
by region product;
run;
data newshoes;
set newshoes;
product=strip(product);
region=strip(region);
run;

/*output*/
title;
footnote; 
ods results off;
ods listing close;
ods escapechar = '^';
ods excel file="C\test.xlsx" 
 style=excel
		options 
    (row_repeat='header'
		 frozen_headers="ON" 
		 autofilter ="all"
		 frozen_rowheaders="ON" 
		 ROWBREAKS_INTERVAL= 'OUTPUT'
		 sheet_label=' '
		 embedded_titles='YES'
		 FitToPage='ON'
		 orientation="landscape"
		 flow="ROWHEADERS"	);

ods excel options (sheet_name="Testcolor") ;
proc report data=newshoes nowd split='*' missing style(header)=[just=center];;
title 'test';
column region showreg product showprod sales inventory returns;

define region/display;
define showreg/computed;
define product/display;
define showprod/computed;
define inventory/display;
define returns/display;

compute showreg/character length=80;
  showreg=catx('',scan(region,1,' '),'^(style[color=orange font_weight=bold]',scan(region,2,' '),')');
endcomp;

compute showprod/character length=80;
   if scan(product,2,' ') = 'Casual' then do;
	   showprod=catx(' ', scan(product,1,' '),'^(style[color=purple font_wight=bold]',scan(product,2,' '),')');
	end;
	 if scan(product,2,' ') = 'Dress' then do;
	   showprod=catx(' ', scan(product,1,' '),'^(style[color=green font_wight=bold]',scan(product,2,' '),')');
   end;
endcomp;
run;

ods excel close;
ods results on;
ods listing;

ods output excel :

[purpleclothlady]

typo:
ods excel file="C\test.xlsx" should be
ods excel file="C:\test.xlsx"

[Kurt_Bremser]

The first argument to CALL DEFINE must be a column identifier. This means that you can only set attributes for whole columns.

If I wanted to create a report like that, I would first look if it is possible in Excel to set attributes for partial cells.

Then try to somehow see how such a cell is stored (unzip the xlsx file and look at the XML code). You might be able to manually create the tag(s) in the COMPUTE block.

But frankly, I would not bother hurting myself with this. Instead, I would create the whole report in a DATA step as a HTML file, and post that on the website of your SAS server.

[Cynthia_sas]

Hi: I see a few problems with the code you posted that was based on my example using SASHELP.SHOES:
-- font_weight is misspelled as font_wight several times
-- it looks like you have Parentheses around the ESCAPECHAR style string, and it needs to be curly braces. The form of ODS ESCAPECHAR is like this:
ods escapechar='^';
^{style[attribute=value attr2=val2] varvalue }

Notice the open and close curly brackets, not parens. The code I posted worked for me in ODS EXCEL. BTW, I'm not sure why you are turning ODS RESULTS OFF. That could have ill effect.

  One cell in Excel cannot have 2 background colors. If Excel won't let a cell have 2 different background colors, SAS can't change how Excel works. Let's take SAS out of the picture. The text within an Excel cell can have 2 different FOREGROUND or FONT colors. That is what ODS ESCAPECHAR is doing in my example. But try this as an experiment. go into Excel. Type several lines into a cell like this:

If you highlight one of the lines and try to format cells, you will ONLY be presented in Excel with a choice to  change the font characteristics, not the background fill.

after changing the text font to be different colors, I get this:

Now, if I highlight the cell and then choose Format Cells, I can change the background fill for the entire cell:

(changed the font back to black so it would not look too garish).

   SAS is out of the picture in the above example, this is purely Excel formatting. And Excel doesn't allow one cell to have 2 backgrounds.

  @Tom is correct. the first argument to CALL DEFINE has to be a column name (or the special tokes _COL_ and _ROW_ or an absolute column number). You have this:

 
call define("compress(substr(dt,12,8))",'style','style = [background = yellow]');
call define("compress(substr(bdt,12,8))", 'style','style = [background = yellow]');

That is not going to work for 2 reasons: 

1) from the SAS/PROC REPORT end of things, you can't use a variable value from SUBSTR as the first argument to CALL DEFINE 

2) from the Excel side of things, you can't have 2 different backgrounds in one cell in Excel 

 I guess I don't understand the purpose of having 2 different background colors in one cell. 

 But, with both SAS and Excel 2 different variable values in 2 different cells could have 2 different backgrounds:

and I can change the backgrounds (for an entire cell) conditionally like this: 

  Hope this helps.

Cynthia