DATA Step, Macro, Functions and more

Proc Report, sparklines

Reply
Frequent Contributor
Posts: 96

Proc Report, sparklines

[ Edited ]

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 ?

 

 

 

Frequent Contributor
Posts: 96

Minimal working example

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 S...

 

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;

 

SAS Super FREQ
Posts: 8,864

Re: Proc Report, sparklines

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
Frequent Contributor
Posts: 96

Re: Proc Report, sparklines

Posted in reply to Cynthia_sas

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!

 

Frequent Contributor
Posts: 96

Re: Proc Report, sparklines

Posted in reply to Cynthia_sas

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

Large teams could take up to 3 pages.

 


fake report.png
Frequent Contributor
Posts: 96

Re: Proc Report, sparklines

Posted in reply to Cynthia_sas

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

 

SAS Super FREQ
Posts: 8,864

Re: Proc Report, sparklines

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
Frequent Contributor
Posts: 96

Re: Proc Report, sparklines

Posted in reply to Cynthia_sas
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!
Ask a Question
Discussion stats
  • 7 replies
  • 491 views
  • 0 likes
  • 2 in conversation