How can I incorporate Excel like data bars in ODS Output?

[SASGeek]

Hi all,

I need to convert an Excel report into SAS. I suspect I can create the 4 side by side charts (or think I know how I'm going to attack it), but how do I create the data bars? I tried finding examples but can't seem to find any. Can someone provide sample code or point me to sample code and output? The desired output is to be in PDF.

Thank you

Paula

[Kathryn_SAS]

The following paper may be a starting point:

https://support.sas.com/resources/papers/proceedings09/221-2009.pdf

Here is a similar Communities post:

https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-Colour-Columns-as-Data-Bars-and-by-Usin...

[PaigeMiller]

https://communities.sas.com/t5/ODS-and-Base-Reporting/How-to-Colour-Columns-as-Data-Bars-and-by-Usin...

[Ksharp]

You want this ?

data a;
input a $ actual goal   ;
v=actual/goal;
format v percent7.1;
cards;
a 850 1200
b 150 1500
c 450 1600 
d 650 1000
;
proc sql;
create table _a as
select *,max(v) as max from a;

create table want as
select * from _a
union all
select 'TOTALS',sum(actual),sum(goal),sum(actual)/sum(goal),. from _a
;
quit;


%let path=c:\temp\;  *the path name stored image;
%macro get_bar(value=,imagename=);
data _null_;
set _a;
if _n_=1 then call symputx('max',max);
run;
ods _all_ close;
ods listing gpath="&path." image_dpi=300;
ods graphics /noborder reset=index width=100px height=15px imagename="&imagename" outputfmt=png;
proc sgplot data=_a noborder noautolegend ;
where a="&value.";
hbarparm category=a response=v/ filltype=GRADIENT displaybaseline=off
 barwidth=1 outlineattrs=(color=green) fillattrs=(color=white) fillendcolor=green;
scatter x=max y=a/datalabel=v labelstrip  datalabelpos=left datalabelattrs=(size=22) markerattrs=(size=0);
xaxis min=0 max=&max. offsetmin=0 offsetmax=0 display=none;
yaxis offsetmin=0 offsetmax=0 display=none;
run;
%mend;
data _null_;
set a;
call execute(catt('%nrstr(%get_bar)(value=',a,',imagename=',a,')'));
run;

ods pdf file='c:\temp\want.pdf' style=htmlblue dpi=300;
proc report data=want nowd style(header)=header ;
column ('Region' a  actual goal v dummy);
define a/display;
define dummy/computed '%Covered' ;
define v/display noprint;
compute dummy/character length=10 ;
 dummy=' ';
 if a ne 'TOTALS' then call define(_col_,'style',"style={preimage='c:\temp\"||strip(a)||".png'}");
  else do;dummy=vvalue(v);call define(_row_,'style','style=header');call define(_col_,'style','style=header{just=r}');end;
endcomp;
run;
ods pdf close;

[Quentin]

Very nice @Ksharp .

Would be good to wrap the macro call in the CALL EXECUTE inside %NRSTR(), to avoid the call execute timing issue where it will try to resolve &max before the macro variable has been created.  Something like:

call execute(catt('%nrstr(%get_bar)(value=',a,',imagename=',a,')'));

I would also explicitly make &max a local variable (it should be local when the timing works out).

If I were making the bars, in the sgplot I would rather set xaxis max=1, so that when the actual/goal is 50% the bar takes up 50% of the space.  But what you've done is consistent with what the OP asked for.

Nicely done!

[Ksharp]

Yeah. You are right. I must forgot to check the LOG . Code has been updated . Thx!