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;
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.
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:
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.
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.
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:
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: