BookmarkSubscribeRSS Feed
morglum
Quartz | Level 8

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 ?

 

 

 

7 REPLIES 7
morglum
Quartz | Level 8

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;

 

Cynthia_sas
SAS Super FREQ
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
morglum
Quartz | Level 8

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!

 

morglum
Quartz | Level 8

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

Large teams could take up to 3 pages.

 


fake report.png
morglum
Quartz | Level 8

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

 

Cynthia_sas
SAS Super FREQ
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
morglum
Quartz | Level 8
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1806 views
  • 0 likes
  • 2 in conversation