BookmarkSubscribeRSS Feed
Calcite | Level 5 TH
Calcite | Level 5

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. 




Super User

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.



Calcite | Level 5 TH
Calcite | Level 5

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. 

Meteorite | Level 14

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;
  vbar type / response=horsepower stat=mean
         group=origin groupdisplay=cluster;

proc sgplot;
  histogram horsepower;
  density horsepower;

ods excel close;

Calcite | Level 5 TH
Calcite | Level 5

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
options( doc="help"
fittopage="yes" /* importamt option */

Super User

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?



Calcite | Level 5 TH
Calcite | Level 5

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.... ;( 



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4 in conversation