topic Re: Need guidance and material in SAS Programming

Need guidance and material

Kottamasu — Wed, 05 Sep 2018 05:25:51 GMT

Hi Team,

Will anyone guide me with the material for generating the drill down graphs in Excel by SAS for daily dash board.

Thanks

Re: Need guidance and material

LinusH — Wed, 05 Sep 2018 06:03:19 GMT

Your question is quite specific, and it seems you have little idea where to start. Sounds that it could be a bit cumbersome for you, on site guidance is usually preferred when starting up such a task.
That said, Google (or the search provider of your choice) is your best friend.

Re: Need guidance and material

Kottamasu — Wed, 05 Sep 2018 06:31:42 GMT

I have tried google to get some help but, unfortunately most of the examples are based on the html and not for the excel.

Re: Need guidance and material

RW9 — Wed, 05 Sep 2018 07:51:23 GMT

So can you show us what you have done so far? For me I didn't even think Excel had drill down graphs? I can't find any information about such a thing. To be honest, if you want an easier life, look at one of the proper reporting suites out there - Spotfire, Tableux, PowerBi, even R and shiny etc. and build your outputs in those which are designed to do this kind of dashboard reporting. Excel is a spreadsheet application, and trying to wedge it into anything else always results in a really dodgy process overall.

Re: Need guidance and material

LinusH — Wed, 05 Sep 2018 07:58:49 GMT

I'm pretty sure you can create drill downs in Excel, witho OLAP cube as a source, and/or with SAS Add-in for MS Office.

This requires SAS BI Server and/or SAS Office Analytics offerings.

Re: Need guidance and material

RW9 — Wed, 05 Sep 2018 08:23:57 GMT

Which is pretty much what I said, plain Excel doesn't have the capabilities, need to add extra layers into it in the form of a BI tool or specific reporting suite

Re: Need guidance and material

Reeza — Wed, 05 Sep 2018 15:09:45 GMT

What's your definition of a drill down graph?

Do you have an example of what you're trying to do so far, with the design finalized?

Do you have SAS Add In for Microsoft Office, or SAS VA? What version of SAS are you using?

What's the current issue you're facing with trying to build this report?

In general, I recommend starting with LexJansen.com and searching for "dashboard", but right now your question is pretty generic so it's hard to understand what you want.

For Excel type reports I usually build it in Excel, you may want to try the PowerPivot Add On or PowerBI which is free for desktop users. Your company may even have a license if they have SQL server.

@Kottamasu wrote:

Hi Team,

Will anyone guide me with the material for generating the drill down graphs in Excel by SAS for daily dash board.

Thanks

Re: Need guidance and material

Kottamasu — Mon, 10 Sep 2018 06:47:03 GMT

Thanks for your quick reply.

I have now tried using the drill down graph in HTML instead of Excel.

Below is my draft code. The issue is chk1.hmtl file has no graph when we open whereas study.html got generated successfully.

Note that I have created the "studydrill" variable in the gdata dataset studydrill='href="xxxxxx.html"'.

goptions gunit = pct xpixels = 850 ypixels = 1050 device=gif transparency noborder ;
filename odsout "xyz/abc";

/* open the html destination for ODS output */
ods html body='chk1.html' path = odsout gpath="xyz/abc";

ods listing close;

axis1 split = "/" ;
axis2 label=("Number of " j=l " Issues");

proc gchart data=gdata;
vbar _name_/sumvar = col1 subgroup = study inside = sum space = 10 width = 10 coutline=black autoref clipref raxis = axis2 maxis = axis1
html=studydrill;
run;

/* open a body file for report */
/* on central sales */
ods html body='study.html' path=odsout;
title1 Issue Details';
proc print data=gdata noobs;
var study dbl _label_ col1;
run;

ods excel close;
/* close the html destination */
ods html close;
ods graphics off;
goptions reset=goptions;