02-04-2016 01:17 PM
I am hitting a wall on getting output in Excel using SGPLOT with sas 9.4 . When I use msoffice2k_x tagsets with GBARLINE, I can save the excel file out and send to others without having html or png files present, and the graphs render. In the same output, SGPLOT needs the html and PNG files in the directory to render. Since I create them I get a hidden link, but the link breaks if I send only the Excel file. I do not relish the idea of having the xls file and 20 png and html files in a directory so the SGPLOT with my bar graphs will render. Again, gbarline does not need that dynamic link, it flattens the graph in excel permentently.
Is there any way I can flatten the excel file to permently link the png images on the tabs so I can send the excel file without all the image files and html files in the directory or location?? I should be able to save a flat file in Excel with all the graphs from SGPLOT like I can do with GBARLINE graphs? I really want to get away from having a directory full of sgplotted graph images and html.
02-04-2016 01:37 PM
Since the graphs are made would another file format such as Word (RTF) or PDF work?
Either of those formats embed the graphics and are a single file.
02-04-2016 01:56 PM
No it will not work for me. I need to create 10-15 tabs, somebody will want to distribute iand split off ndividual tabs downtstream .....They cant send a tab as a new workbook if the image is broken and not on the page with the table itself with SGPLOT. Again, I dont understand the idea of SGPLOT not functioning like GBARLINE. And I cant use GBARLINE since I need 2 bar vars not just one with line vars; so I have to use or am forced to use SGPLOT to get multiple bars for 2 varaibles. The whole point is automation. I can create all those tabs instantly and in a PDF or wordfile but I lose tabs and deliniation options. PDF or word is more for a white paper not to share individual productivity data. and Dashboards lack portability and just are not the goal here. I am trying to cut down on making 20 graphs manually in excel. So much for ODS created Excel using the simple tagsets.
02-04-2016 02:08 PM - edited 02-04-2016 02:10 PM
Here is a simple case I ran at SAS 9.4. It created an excel file with two tabs and one graph in each tab.
ods excel file='c:\cars.xlsx';
proc sgplot data=sashelp.cars;
vbar type / response=horsepower stat=mean
proc sgplot data=sashelp.cars;
ods excel close;
02-04-2016 02:29 PM
This works, and I did try this yesterday, but it didnt seem like I could easily segregate between tabs with multiple breaks? Meaning suppose I want to put a proc report and 2 graphs on tab one, the 1 graph on tab 2, and a proc tabulate and 3 graphs on tab 3??? Do the "excel" ODS tag sets have the same or some structure to bucket my ods sandwich as the ods tagsets.msoffice2k_x sets? example I can create these dividers and put whatever I want in each tab...incrementing them; here is tab 2 for example, that has 2 sqplots and one proc reportt in it...
ods _all_ close;
ods tagsets.msoffice2k_x close;
ods tagsets.msoffice2k_x file="/apps/sas/data/sasuser/users/theaton/&XLS_FILENAME.2.html" gpath="/apps/sas/data/sasuser/users/theaton/" (url="")style=normal
fittopage="yes" /* importamt option */
02-04-2016 03:04 PM
SGPLOT is generated from ODS GRAPHICS, while I'm fairly certain GBARLINE is under SAS GRAPH so the foundation for the two procs are quite different and you're seeing some of that.
Have you tried ODS EXCEL with the startpage option?
02-04-2016 03:43 PM
No but I can look into it...It just is a pain to reinvent the wheel , wheich I know I may have to do if I want SGPLOT to embed static images in Excel. Otherwise I guess I have go with a fat directory full of the pieces to render the graphs from SGPLOT in excel.... ;(