topic Re: ODS Excel - Insert image within cell in ODS and Base Reporting

ODS Excel - Insert image within cell

KonstantinVasil — Mon, 25 Feb 2019 14:05:56 GMT

Since ODS Excel doesn't support PREIMAGE and POSTIMAGE, I am looking for a simple way to implement image insertion within cell.

The paper below discusses solution in "UNIX operating environments via the Java object in the DATA step to post-process a worksheet to which you are adding an image." However, the example is only for Windows.

The ODS Excel Destination versus the ODS ExcelXP Tagset

Is there any simple way to insert image within a cell possibly without post-processing?

For example, insert image depending on Horsepower variable:

data temp; set sashelp.cars;
	if make="Audi";
	test=0;
run;

ods excel file= "&excel_export_path."  ;
	proc report data=temp nowd panels=2 SPLIT='{' spanrows;
	  	column ('Test' model Horsepower test);
		Compute test;
			 If Horsepower > 200 then  call define(_col_,"style","style=[preimage=Green.gif]");
			 Else if Horsepower < 200 then call define(_col_,'style',"style=[preimage=Red.gif]");
		endcomp;
	run; 
ods excel close;

Re: ODS Excel - Insert image within cell

RW9 — Mon, 25 Feb 2019 14:24:41 GMT

SAS have provided more functionality with ods excel/tagsets.excelxp, however I don't see it covering all the operation of a third party software anytime soon. Do be aware that unless your setting the background image property of a cell, the image would generally be in a image box, hence not even part of the data. Afraid my advice is not going to change:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Multiple-horizontal-reports-on-same-Excel-sheet/m-p/537744#M22413

If you want to use specific Excel functionality, learn to create templates and use VBA code.

If you want to provide good interactive visualizations, use a visualization software - like spotfire, SAS VA, Tableua, PowerBI.

Re: ODS Excel - Insert image within cell

SuryaKiran — Mon, 25 Feb 2019 14:44:21 GMT

Hello @KonstantinVasil,

What environment do you have, Unix/Linux or Windows?

Unfortunately, As of now ODS Excel can't insert images in cell level directly. If your on Windows environment, then you can try the %Excel_Enhance macro that was mentioned in paper Insights from a SAS Technical Support Guy:
A Deep Dive into the SAS® ODS Excel Destination.

Also check this blog for other ways to insert image in excel.

Re: ODS Excel - Insert image within cell

Cynthia_sas — Mon, 25 Feb 2019 15:56:40 GMT

Also, the example you posted looks like it is using RED.GIF and GREEN.GIF to color a cell. There are other ways to do that particular type of traffic lighting without using images. What exactly is it that you want to do? Traffic light with colors? or insert a picture?
Cynthia

Re: ODS Excel - Insert image within cell

KonstantinVasil — Mon, 25 Feb 2019 16:30:26 GMT

The environment is Unix/Linux - as I mentioned in the referenced link there is only example for Windows

The pictures which I am using aren't only colors but tick and cross traffic light signs - in fact, they are the same that Excel has as Icon Set for conditional formatting:

Re: ODS Excel - Insert image within cell

SuryaKiran — Mon, 25 Feb 2019 19:30:57 GMT

Try inserting unicodes instead of the images. You may need to find the unicode values for the icons you want.

ods escapechar='^';
proc report data=sashelp.class;
column Name Sex Symbol Age ;
define Symbol/computed;

compute Symbol/character length=50;
	if sex='M' then do;
		Symbol="^{unicode '2642'x}";
		call define(_col_,'style','style={color=red font_face="Arial Unicode MS" font_size=24pt}');
	end;
	else do;
		Symbol="^{unicode '2640'x}";
		call define(_col_,'style','style={color=green font_face="Arial Unicode MS" font_size=24pt}');
	end;
endcomp;
run;

If your interested, I searched here for unicodes.

Re: ODS Excel - Insert image within cell

KonstantinVasil — Tue, 26 Feb 2019 09:23:02 GMT

Thank you. The unicode work-around is clever although that the symbols are not exactly the same.

However, I run into some problems when the variable to which I am comparing is numeric. For example, the code below does not work unless Weight is defined as group or order. In my case, this is not a solution, because I have other group variables with spanrows and I do not want Weight to be spanned. Is there some way to assign Symbol based on a numeric variable that is not order or group variable?

ods escapechar='^';
proc report data=sashelp.class;
	column Name Sex Weight Symbol Age ;
	define Symbol/computed;
	/*define Weight/group;*/
	compute Symbol/character length=50;
		if Weight>100 then do;
			Symbol="^{unicode '2642'x}";
			call define(_col_,'style','style={color=red font_face="Arial Unicode MS" font_size=24pt}');
		end;
		else do;
			Symbol="^{unicode '2640'x}";
			call define(_col_,'style','style={color=green font_face="Arial Unicode MS" font_size=24pt}');
		end;
	endcomp;
run;

Re: ODS Excel - Insert image within cell

Cynthia_sas — Tue, 26 Feb 2019 15:52:16 GMT

Hi, if you want WEIGHT to be an analysis variable, with a default statistic of SUM, then the correct reference in the COMPUTE block would be
if weight.sum > 100

This is a compound name that PROC REPORT requires when your numeric variables are used as analysis variables on the report. Otherwise, if you are not calculating report breaks (grand totals) or group breaks (subtotals), then you can change the usage of WEIGHT to display and then you can use the simple
if weight>100
in your COMPUTE block.
Cynthia