Data visualization with SAS programming

Problems using producing static Excel output with SGPLOT vs GBARLINE

Reply
New Contributor TH
New Contributor
Posts: 4

Problems using producing static Excel output with SGPLOT vs GBARLINE

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. 

 

 

 

Grand Advisor
Posts: 10,210

Re: Problems using producing static Excel output with SGPLOT vs GBARLINE

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.

 

 

New Contributor TH
New Contributor
Posts: 4

Re: Problems using producing static Excel output with SGPLOT vs GBARLINE

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. 

SAS Super FREQ
Posts: 1,080

Re: Problems using producing static Excel output with SGPLOT vs GBARLINE

[ Edited ]

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;

New Contributor TH
New Contributor
Posts: 4

Re: Problems using producing static Excel output with SGPLOT vs GBARLINE

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");

Grand Advisor
Posts: 17,338

Re: Problems using producing static Excel output with SGPLOT vs GBARLINE

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?

 

 

New Contributor TH
New Contributor
Posts: 4

Re: Problems using producing static Excel output with SGPLOT vs GBARLINE

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

Ask a Question
Discussion stats
  • 6 replies
  • 430 views
  • 0 likes
  • 4 in conversation