topic Re: Sparklines w/SAS in Graphics Programming

Sparklines w/SAS

Spell — Wed, 04 Jul 2012 14:29:32 GMT

looking for options creating Sparkline graphs with SAS. Also need to embed hyperlink in either the line or verbiage/label.

Re: Sparklines w/SAS

Jay54 — Wed, 04 Jul 2012 16:06:53 GMT

See my blog article: http://blogs.sas.com/content/graphicallyspeaking/2011/12/06/adding-a-spark-to-your-data/

Re: Sparklines w/SAS

Spell — Wed, 04 Jul 2012 17:57:48 GMT

Sanjay, this looks very promissing. appropriate subject matter also, eh?

Thanks!!!

Re: Sparklines w/SAS

MikeZdeb — Wed, 04 Jul 2012 18:32:51 GMT

hi ... Sanjay's posting seems to answer your question using SG

if you'd like to see some non-SG soutions, these are well worth a look ...

an example that's in "wizard" Robert Allison's new book ... SAS/GRAPH: Beyond the Basics

http://robslink.com/SAS/democd48/stock_table.htm

and the accompanying text (with a link to the SAS code) ...

http://robslink.com/SAS/democd48/stock_table_info.htm

also, for the IPhone ...

http://robslink.com/SAS/democd38/iPhone_dashboard_info.htm

a neat paper by Ted Conway ... %SPARKY: A SAS® Macro for Creating Excel Sparklines

SAS Community ... http://www.sascommunity.org/wiki/Sparklines

(just use Google and search for SAS sparkline ... you'd be surprised how much is available ... though usually with more SAS code than what's in Sanjay's answer, but I think with a lot more possibilities of customization)

Re: Sparklines w/SAS

Jay54 — Wed, 04 Jul 2012 18:49:10 GMT

See new blog article on how to create a simple Spark Line Table using the SGPLOT procedure.

Re: Sparklines w/SAS

Spell — Tue, 10 Jul 2012 15:52:47 GMT

Sanjay, I got this Sparkline graph to work with my data. Now I'm trying to get it posted on the SAS BI Portal and seem to loose the url links? I'm using EG and Publishing this to the Report Repository, then adding it to the Portal in a SAS Report Portlet. Any suggestions will be greatly appreciated!

Thanks again!

Re: Sparklines w/SAS

Jay54 — Tue, 10 Jul 2012 17:58:10 GMT

I can't say what might be the problem. I assume you are using the code with the html output and the settings on the ODS graphics statement to enable emage maps for the links. I will ask around.

Re: Sparklines w/SAS

CaseySmith — Tue, 10 Jul 2012 21:58:40 GMT

If you don't see the links in the results in EG, you may be encountering this:
EG wraps submitted code with ODS statements for the requested results type (SASReport by default). You may have one or more results formats checked in EG's Tools->Options->Results->Results General->Results Formats, resulting in nested ODS statements, since the sample code already contains ODS statements (can confirm in the log). To avoid, you can do either of the following:

-uncheck all the Results Formats (in EG's global Tools->Options->Results->Results General->Results Formats or from the specific Program's Properties) and resubmit the code
-remove the ODS HTML open/close from the code, check HTML results format in EG options, then resubmit the code

I tried it out and both of those worked for me.

Re: Sparklines w/SAS

Spell — Wed, 11 Jul 2012 12:36:54 GMT

getting closer! appologies for my novice understanding of SAS Graph. see code below. I did turn off the output options in EG, then it asks me to download the results "s:\Projects\Development\Randy\GraphOutput\graph1.html" which I do and the url links work there (in EG land). however, when I go do the directory where this is stored and open the file, the links do not work. There is also in that directory the imagemap "Spark Table SG URL". I'm not sure how these are connected such that I can put them in a Portlet on the Portal (and what kind of Portlet) so they work together to render the Sparkline graph iwth operable URL links?

thanks for all the help with this, this is a critical point for this project, and many future ones.

%let gpath='S:\Projects\Development\Randy\GraphOutput';

ods listing gpath=&gpath image_dpi=100 ;

ods html path="S:\Projects\Development\Randy\GraphOutput" body="graph1.html";

ods graphics / reset width=7.0in height=4.0in antialiasmax=1000

imagename='Spark_Table_SG_URL' imagemap tipmax=1000;

title 'Quality ScoreCard Sparkline Graph';

proc sgplot data=Tmp3 noautolegend;

refline ref / lineattrs=(thickness=27) transparency=0.75;

series x=x y=y / group=group lineattrs=graphdatadefault(pattern=solid);

scatter y=group2 x=yRatelabel /markerchar=Rate x2axis url=url;

scatter y=group2 x=yBaseLabel /markerchar=Base x2axis url=url;

scatter y=group2 x=yTargetLabel /markerchar=Target x2axis url=url;

scatter y=group2 x=yStretchLabel /markerchar=Stretch x2axis url=url;

xaxis display=none offsetmax=0.20 type=discrete;

x2axis display=(nolabel noticks) offsetmin=0.7;

yaxis values=(1 to 4) display=(nolabel noticks); * type=discrete;

run;

ods html close;

Re: Sparklines w/SAS

Spell — Wed, 11 Jul 2012 13:45:24 GMT

A couple points for further clarification:

when I run the code in EG, I can then download to view "'S:\Projects\Development\Randy\GraphOutput' and it shows the graph with url links that work
when I go to the directory where I am storing this 'S::\Projects\Development\Randy\GraphOutput' the file "Spark_Table_SG_URL.png" file renders the Sparkline graph but with no links operating and the file "graph1.html" does nto render anything but a blank page with "The SGPlot Procedure" at the top.

thanks.

Re: Sparklines w/SAS

DanH_sas — Wed, 11 Jul 2012 14:16:44 GMT

Try adding (URL=NONE) after the path to see if it helps your deployment issues:

ods html path="S:\Projects\Development\Randy\GraphOutput" (url=none) body="graph1.html";

Re: Sparklines w/SAS

Spell — Wed, 11 Jul 2012 14:59:30 GMT

Ah, now I can open that file from the directory and the links work. Thanks! Now I'm not sure how to get this on the Portal page so that it displays and works there?

Thanks for you help with this!

Re: Sparklines w/SAS

Cynthia_sas — Wed, 11 Jul 2012 17:45:18 GMT

Hi:

When you say "get this on the Portal page so that it displays and works there", you could mean a variety of different scenarios.

1) you could want the static png file and HTML file to be stored on the same server as your Portal and you will give your users a URL for them to open the static files

2) you could want the static png file and HTML file to be published to a webDAV storage location (such as a Xythos webDAV repository) that was accessible from the Portal

3) you could want to turn your program into a stored process, so that each user could run the stored process with different input parameters and surface their results

4) you could be creating a custom BI web application that would surface the results dynamically, but using a custom front end.

Right now, you are writing your output to your S: drive, which is either a drive that is mapped to your Windows machine or is a drive physically attached to your Windows machine. So, let's look at #1 scenario. If you have write access to your web server machine and if your Portal is on that same web server, then you could just FTP or copy (assuming you have the appropriate permissions) from your S: drive to your web server machine (because it's unlikely that your web server machine or your Portal machine have visibility of your personal S drive). Remember that in this scenario, you have to copy the HTML file and the PNG file together and put them together in the same directory on your server.

Let's say that the UNC name of the report directory on your web server is \\elmo\root\public\reports\Randy and that the URL to access that directory via the http protocol is: http://www.elmo.com/reports/Randy OK...so if you FTP both files to the web server location, and the name of your HTML file is graph1.html, then someting like this URL should open your report, assuming you copied both files to the right directory and you have the correct URL for accessing that directory:

http://www.elmo.com/reports/Randy/graph1.html

If you are a lucky person, your server administrators will give you write access (under #1 scenario) to the Randy directory on the server. Perhaps you can get to \\elmo server from your Windows machine or maybe you can map to your directory on the \\elmo server. If you can write directly to your server machine from your SAS job, then you could try code like this:

ods html path="\\elmo\root\public\reports\Randy" (url=none) body="graph1.html";

OR, let's say that \\elmo\root is mapped as the Q drive to your Windows machine and that you have the appropriate permissions:

ods html path="Q:\public\reports\Randy" (url=none) body="graph1.html";

But if you cannot write directly to your server machine, then you are in the business of copying or using FTP from your personal S: drive to the server machine. And, you have to get all those permissions granted, etc, etc.

#2 scenario is sort of similar to #1, but you would have to create your output and then publish the output to the webDAV repository using the Publishing framework and probably making a SAS package of your results.

#3 scenario means that you would NOT publish results, you would, instead turn your program into a stored process that could be run dynamically from the Portal page by each user. So that when they make a request, the program runs, on demand. If you define input parameters in your stored process (such as a value for GROUP or GROUP2), then the user-specified values could be used to create customized output. If you defined your program as a stored process, it could be submitted from within the Portal or from any of the client applications (such as the Add-in for Microsoft Office) on the BI Platform.

#4 scenario means that you would probably be creating a custom Java application or JSP page to surface in the portal. Your program would probably still be defined as a stored process, but instead of using the default interface for the stored process, you would use a custom interface that you wrote.

Depending on what you want to do, now that you have a working program is talk to your SAS administrator or Portal administrator to find out the best way to surface your report. If you will be making a stored process or a custom web application, then you'd need to look at the Stored Process Developer's Guide or work with Tech Support to get your program converted appropriately to run as a stored process. If you want to publish to a webDAV repository, using the SAS Publishing Framework, again, Tech Support or the documentation is your best bet.

But, now that you have a working SAS program, the hardest part of what you want to do is over.

cynthia

Re: Sparklines w/SAS

Spell — Thu, 12 Jul 2012 17:02:39 GMT

Cynthia, thanks! this is good. I like the idea of writing directly to the web server as this will help with automation of the processing.

I was able to publish the file using the Publishing framework and surface to the Portal. The issue I have with this approach is that the end-user will have to select the "Launch Live Report" button (left side of the portlet) to open the report in WRS for the link to work. seems a bit cumbersome.

I created a Stored Process out of the code, w/no prompts. This works in EG but when I post to the Portal in a Stored Process Portlet it doesn't seem to render the output. I have other STPsthat do have prompts and they do work as expected. probably some wierd issue I'll figure out.

I want to thank all of you for contributing to this post, it has been very helpful and extremely valuable! Using the power of SAS Graph to go beyond the canned reporting capabilities in SAS BI, then tie that into the BI Portal, is very powerful for those requiring advanced graphics for their Dashboards. I see there has been quite a few others that have viewed this post and hopefully they will get some valuable insight as well.

Thanks again!!