topic Re: Graphs generated in excel through SAS coding in SAS Enterprise Guide

Graphs generated in excel through SAS coding

Thanu — Mon, 21 Aug 2017 11:10:35 GMT

Hello everyone,

My purpose is to create a LINE chart with points (like in the below attachment.) in sas and export it to excel in one go. I need the charts to be interactive, meaning if user changes one of the numbers then the charts change.(Instead of a picture of a chart. But please let me know what can be accomplished).I understand that a macro needs to be created to read the dataset into it and then use this macro variable while creating charts so that the chart is interactive. But I am not sure how is this done. please help.
Attached is the excel sheet in which I have a sample of my dataset and the interactive graph .

(In the final output in excel, I dont need the data set on the graph page, just the interactive graph is good to go. )

Re: Graphs generated in excel through SAS coding

RW9 — Mon, 21 Aug 2017 11:21:02 GMT

Hi,

Please avoid attached files as they are a security risk, I wouldn't download them.

What I take from your question is that you need an interactive Excel graph. The only real way to do this is via Excel itself. From your SAS session you would drop the data out, it is within the Excel file (using either the graph builder and fixed cells, or by VBA) to re-create the graph in the file. This is not something which SAS can do for you. What a lot of people use if this kind of thing is needed is web reports or something similar, this is a web front end where parameters can be entered and graphs and listings created - these are called visualisations. This would be the way to go if there is to be much use of this process. If it is just this one file, then look at using a macro enabled Exce file, with some VBA code which re-creates a graph from given parameters either through a form or cells - this is outside the scope of this forum, but to start:

https://www.thespreadsheetguru.com/blog/2015/3/1/the-vba-coding-guide-for-excel-charts-graph

Its very simple. If you need to keep your data linked to this, then export your data from SAS to a file (CSV, XLSX) and have your main VBA enabled file load that file to get the latest data.

Re: Graphs generated in excel through SAS coding

Reeza — Mon, 21 Aug 2017 17:22:26 GMT

If the chart and table are always the same 'style' or type you can create an XLSX template file and use SAS to replace the data in the file.

You first copy the template, then export your data.

If you don't have a 'fixed' template then it's a much more difficult problem.