topic Re: Proc Report, sparklines in SAS Programming

Proc Report, sparklines

morglum — Mon, 07 Dec 2015 22:10:12 GMT

Hi everyone,

I am both a Proc REPORT and a sparklines newbie.

I am currently working on a report that will display information about workers.

I would like to display a row for each workers, showing whether or not they achieved their weekly objectifve. This would simply involve displaying columns, one for the objective (Say 20 sales) and the result (say 23 sales).

I would also like to display a win-loss sparkline showing whether or not thelast 52 weekly objectives have been met or not.

Workers have a few of these weekly objectives, but being able to display 3 of them on a single row would be perfect.

A manager can have between 10 and 20 workers, so I believe this should be able to fit in a single page.

How would you proceed? So far, my best idea would be to generate a sparkline "winloss" GIF using the %SPARKY macro linked above and inserting them in the proc report.

Any ideas would be very welcome.

cheers!

bonus question: Can I do a proc report "by ManagerID", or do I have to run it separately for each MAnagerID ?

Minimal working example

morglum — Tue, 08 Dec 2015 21:17:02 GMT

Alright, I've found a 2012 PharmaSUG China paper that has a good start for me.

Using PROC REPORT and ODS for Customized Presentation of Study Results and for Medical Informatics Sparklines Jim Sattler, Satmari Software Systems, Inc., Philippines

I've build a minimal working example (attached), that created sparklines for 25 records and display them next to the worker.

The report adds a line above and below the PNG, which makes for needlessly "high" cells. It is easily seen in the RTF file. Is there a way to fix this?

The sparklines look terrible (low resolution) in the PDF created. My output will have to be a PDF, is there a good way to fix thix?

EDIT: attaching the file didnt work, so here is the code:

*******************************************************************************************************************************************************

data temp;
do subjidn= 1 to 25 by 1;
do t=1 to 10;
y=(5+subjidn-t)**2+50;
output;
end;
end;
run;

data temp;
set temp;
obs= _n_;
run;
title1;

/*MACRO WITH POSITIONAL PARAMETERS USED IN THE PRORAM*/
%macro sparkline (data_set, var_name, upper_limit, lower_limit, minmax);
**varname is the name of the Y variable;
** upper limit is either the 75TH percentile (75TH) or a hardcoded number;
** lower limit is either the 25TH percentile (25TH) or a hardcoded number;
** if MINMAX = "YES" then the minimum and maximum values are identified with a colored dot;

/*CREATE SUMMARY VARIABLES FOR EACH PATIENT*/
proc means data=&data_set noprint;
var &var_name;
class subjidn;
output out=avg(where=(_TYPE_=1)) mean=avg p25=p25 p75=p75 min=min max=max;
run;
/*CREATE MACRO VARIABLES FOR PATIENTS FROM SUMMARY VARIABLES*/
data _null_;
set avg end=lastrec;
call symputx('patient'||left(_N_),subjidn);
if lastrec then call symputx('numpts',_n_);
run;

/*CREATE MACRO VARIABLES FOR THE SUMMARY STATISTICS*/
/*ITERATE FOR THE NUMBER OF PATIENTS COMPUTED ABOVE*/
%do i=1 %to &numpts;
data _null_;
set avg;
where subjidn=&&patient&i;
call symputx('avg',avg);
call symputx('p25',p25);
call symputx('p75',p75);
call symputx('min',min);
call symputx('max',max);
run;

/*CONVERT MACRO SUMMARY STATISTICS VARIABLES INTO INPUT VARIABLES FOR SPARKLINE
GRAPHICS*/
data spark;
retain labtest;
set &data_set(where=(subjidn=&&patient&i)) end=last;
if &var_name ne . then labtest=&var_name;
minmax="&minmax";
if (minmax="YES" & &var_name=&min) then min=&min;
if (minmax="YES" & &var_name=&max) then max=&max;
u="&upper_limit";
if u='75TH' then ul=&p75;
else ul="&upper_limit";
l="&lower_limit";
if l='25TH' then ll=&p25;
else ll="&lower_limit";
x=_n_;
z=&avg.;
if last then e=labtest;
call symputx('lastone',round(e,0.1));
run;

/*CREATE MACRO VARIABLES FOR NAMES OF OUTPUT SPARKLINES
WITHIN SAME DO LOOP ITERATION*/

data _null_;
call symputx('name',"&data_set"||left(&i));
run;
filename odsout 'c:\temp\';
%put name=&name.;
/*USE SUMMARY VARIABLES AS INPUT FOR PROC SGPLOT*/
ods graphics / reset noborder width=1.95in height=.3in imagename="&name";
ODS LISTING GPATH = 'c:\temp';
proc sgplot data=spark noautolegend;
band x=x lower=ll upper=ul / fillattrs=(color=grayaa) ;
series x=x y=&var_name / lineattrs=(color=black thickness=2px) ;
series x=x y=z
/ lineattrs=(color=black thickness=2px)
curvelabel="&lastone"
curvelabelattrs=(size=12pt color=red);
scatter x=x y=e / markerattrs=(symbol=circlefilled color=red ); *last point (red dot);
scatter x=x y=min / markerattrs=(symbol=circlefilled color=blue ); *minimum (blue dot);
scatter x=x y=max / markerattrs=(symbol=circlefilled color=green ); *maximum (green dot);
xaxis display=none;
yaxis display=none;
run;
quit;

%end;
%mend;
%sparkline(temp, y, 75TH, 25TH,YES);

data subjidn;

do subjidn=1 to 25;
output;
end;
run;

ods pdf file="c:\temp\mypdf.pdf" notoc;
ods rtf file="c:\temp\myrtf.rtf";
proc report data=subjidn nowd ;
column subjidn sparkline ;
define subjidn / display;
define sparkline / computed format =$1. style(column)=[cellwidth=2in];
compute sparkline;
sparkline="";
call symputx('count',subjidn);
call define(_col_,'style','style=[preimage="c:\temp\temp&count.1.png"]');
endcomp;
run;
quit;
ods pdf close;
ods rtf close;

Re: Proc Report, sparklines

Cynthia_sas — Tue, 08 Dec 2015 22:15:47 GMT

Hi--
You will find a simple win/loss sparkline that uses the Bissantz Spark Fonts in my paper Creating Complex Reports http://www2.sas.com/proceedings/forum2008/173-2008.pdf on page 12.

cynthia

Re: Proc Report, sparklines

morglum — Tue, 08 Dec 2015 22:35:00 GMT

Hi Cynthia,

Thanks for the reply! I've read that paper already, but I couldnt find the Bissantz font anywhere. Also, I'm very interested in a "bar" sparkline.

cheers!

Re: Proc Report, sparklines

morglum — Tue, 08 Dec 2015 22:44:01 GMT

Here's a mock-up of the report I am trying to build.

Large teams could take up to 3 pages.

Re: Proc Report, sparklines

morglum — Fri, 11 Dec 2015 15:43:24 GMT

Hi Cynthia,

Thanks for replying. I've been wondering why in that paper you say that these reports are typically output to RTF instead of PDF or HTML?

Is it because you do post-processing before converting to PDF? Because my clients typically want a PDF, not a word file.

cheers

Re: Proc Report, sparklines

Cynthia_sas — Fri, 11 Dec 2015 16:21:36 GMT

Well, the Bissantz SparkFonts were initially a font plug-in for Word. And at the time I wrote the paper, most of my sparkline examples were documents that were being edited after creation. That is a 7 year old paper -- and I would expect that HTML and PDF are probably just as popular.

Also, at the time I wrote the paper, ODS GRAPHICS was not production and the SG Procedures did not exist. For example, this blog shows sparklines using ODS GRAPHICS: http://blogs.sas.com/content/graphicallyspeaking/2012/07/04/spark-lines-made-easy/

and look on page 13 of this paper. If you made your bar chart very small and the right size, then you could insert it into your PROC REPORT output as shown here: http://www.lexjansen.com/nesug/nesug13/122_Final_Paper.pdf

cynthia

Re: Proc Report, sparklines

morglum — Fri, 11 Dec 2015 21:17:38 GMT

That last report (and its code) is exactly what I was looking for! Thanks for pointing me to it, I would never have made it until the part where it mentions sparklines..

cheers!