BookmarkSubscribeRSS Feed
TH
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. 

 

 

 

6 REPLIES 6
ballardw
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.

 

 

TH
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. 

Jay54
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 data=sashelp.cars;
  vbar type / response=horsepower stat=mean
         group=origin groupdisplay=cluster;
run;


proc sgplot data=sashelp.cars;
  histogram horsepower;
  density horsepower;
run;

ods excel close;

TH
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"
sheet_name="two"
orientation="portrait"
fittopage="yes" /* importamt option */
print_fitheight="1"
print_fitwidth="1"
page_break="yes"
papersize="letter"
leading_zero="yes"
embedded_titles="yes"
embedded_footnotes="yes");

Reeza
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?

 

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1203 views
  • 0 likes
  • 4 in conversation