Data visualization with SAS programming

excel file with gchart output to be properly displayed over the network

Reply
N/A
Posts: 0

excel file with gchart output to be properly displayed over the network

Hello All,

I want to create a excel with proc gchart output, i am using the below code for the same, It works fine.
However when I open the excel file over the network from some other machine, i see a cross inplace of excel file
___________________________________________________________
ods listing close;

ods html
path="C:\Sunil\Ministry ODS\test" (url=none)/* HTML output directory */
body="test.htm" /* HTML filename */
gpath="C:\Sunil\Ministry ODS\test\images" /* graphics output file location */
;
goptions reset=all device=actximg;
proc gchart data=sashelp.class;
hbar3d sex / sumvar=height type=mean;
run;
quit;

ods html close;
ods listing;
__________________________________________________________

Later i tried to insert the image in excel using the below DDE script, Now if i access the file over network i am able to the see the graph in excel.

____________________________________________________________
OPTIONS NOXWAIT NOXSYNC;

x ' "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" ';


DATA _NULL_;
rc = SLEEP(2);
RUN;


filename ddedata DDE "excel|system";
data _null_;
file ddedata;
put '[open("C:\Sunil\Ministry ODS\test\htmltest.xls")]';
put '[Select("R1C1")]';
put '[INSERT.PICTURE("C:\Sunil\Ministry ODS\test\gchart11.png")]';
run;
_______________________________________________________________

Now my problem lies here, in the code to insert pictures in excel i need to give a image name and my code by default give image name as gchart1.2 and so on.

Please help me with this, i just want a excel file with gchart output to be properly diaplayed when accessed from other machines over the network.

If there is some other approach to fix this, please let me know.
N/A
Posts: 0

Re: excel file with gchart output to be properly displayed over the network

### a small correction, i used the below ODS command to get excel output
ods html
file="C:\Sunil\Ministry ODS\test" /* HTML output directory */
body="htmltest.xls" /* HTML filename
gpath="C:\Sunil\Ministry ODS\test" /* graphics output file location */
;
SAS Employee
Posts: 963

Re: excel file with gchart output to be properly displayed over the network

Hopefully someone who knows the DDE/Excel end of things might give a definitive reply, but I'll give my "best guess" here ...

I notice you're using device=actximg, and this most likely creates a .png file on your hard disk, and (I assume?) the excel spreadsheet points to that image, rather than having a copy of the png 'inside' the excel spreadsheet(?)

If that's the case, then the problem is that the other machine probably doesn't have that same folder/png-file on its c: drive, and therefore excel just displays a red 'x'.

This is just my _guess_ - I haven't experimented with DDE/Excel and SAS/Graph output before...



Oh, and to answer the other part of your question, I think you can use the des= option to control the name of your .png file, such as ...

hbar3d sex / sumvar=height type=mean des='foo'; Message was edited by: Robert Allison @ SAS
Super Contributor
Super Contributor
Posts: 3,174

Re: excel file with gchart output to be properly displayed over the network

Your ODS HTML code must consider the CODEBASE= parameter and also the URL= parameter as well.

The unanswered question is how are you intending to deliver the HTML content (documents) - possibly generate them to a shared web server with a drive/path location accessible to other users? Clearly, you cannot use C:\ as the destination drive/folder/directory location because each of the other recipients will also have their own like-named drive and so the content will not have been generated there. Or, less likely, your machine is running a web server and the output location for your generated HTML and image content must be directed to a sub-area accessible to the web server, using these same-named parameters.


Scott Barry
SBBWorks, Inc.
Super Contributor
Super Contributor
Posts: 3,174

Re: excel file with gchart output to be properly displayed over the network

Recommended Google advanced search argument for related content hosted on the SAS support website.

ods html codebase url web content site:sas.com

Scott Barry
SBBWorks, Inc.
SAS Employee
Posts: 963

Re: excel file with gchart output to be properly displayed over the network

Scott,

I don't think the ods codebase= comes into play in this particular situation.

If it were dev=java it could be important, to point to location of the jar files ar viewing/rendering time. Or if it were dev=activex you could use codebase= to point to the executable of the sas/graph activex graph control installer, which would prompt the user to install it at viewing/rendering time (if they don't already have it installed). ... but in this case, with dev=actximg, the rendering is done when the original sas job is executed on the server (and the sas/graph activex control is automatically/guaranteed to already be installed there, via the sas install), and when people are viewing the graph later, they're just viewing a .png file (and don't need the sas/graph activex graph control for that).

Of course, if he decides to deliver them in a different way (dev=java or dev=activex), then he'll need to start using a codebase that's accessable by all the people viewing/rendering the graphs ... but not with his current (dev=actximg) strategy. Message was edited by: Robert Allison @ SAS
SAS Super FREQ
Posts: 8,643

Re: excel file with gchart output to be properly displayed over the network

Hi:
Robert is correct. CODEBASE is not an issue with DEV=ACTXIMG or DEV=JAVAIMG.

In fact, I don't think the DDE solution (although nice) was even necessary. When I use this original code to create the files, this is the URL that gets generated for the <IMG> tag (assuming the first example, where the image is in a subdirectory called IMAGES)
[pre]
<img alt="for Excel" src="C:\Sunil\Ministry ODS\test\images\images\grf2xl.png"
style=" border-width: 0px; height: 480px; width: 640px;" border="0" usemap="##LN00541" class="c Graph">

[/pre]

Which might be incorrect on the server -- especially if it is a Unix or Linux or Mac file server. The image would have to be stored in this -exact- location on the server for the file to load correctly.

However, when I use this code to create the files (note the difference in URL= option value for GPATH) --

[pre]
ods listing close;

ods html
path="C:\Sunil\Ministry ODS\test" (url=none)/* HTML output directory */
body="test.htm" /* HTML filename */
gpath="C:\Sunil\Ministry ODS\test\images" (url='.\images\') /* graphics output file location */
;

goptions reset=all device=actximg;
proc gchart data=sashelp.class;
hbar3d sex / sumvar=height type=mean
name='grf2xl' des='for Excel';
run;
quit;

ods _all_ close;

[/pre]

then a RELATIVE &LT;IMG&GT; tag gets generated
[pre]
<img alt="for Excel" src=".\images\grf2xl.png" style=" border-width: 0px; height: 480px; width: 640px;"
border="0" usemap="##LN00541" class="c Graph">

[/pre]

and the image file loads just fine in the browser and in Excel. The trick is that the PNG file will have to be moved to an IMAGES subdirectory on the network machine and that the IMAGES subdirectory will have to be UNDER the directory where TEST.HTM is located.

If BOTH the IMAGE and the HTML file would be in the same directory location, then the original poster needed to put URL=NONE for -both- the PATH and the GPATH options in order to build a relative &LT;IMG&GT; tag.
[pre]
ods html
path="C:\Sunil\Ministry ODS\test" (url=none)
body="htmltest.xls"
gpath="C:\Sunil\Ministry ODS\test" (url=none) ;

generates this tag in the HTML file:

<img alt="for Excel" src="grf2xl.png" style=" border-width: 0px; height: 480px; width: 640px;"
border="0" usemap="##LN00541" class="c Graph">

[/pre]

Also note that even though you named the file with ".XLS", you are not creating a true, binary Excel file. You are only creating a file that Excel knows how to open and if you try to open this file with Excel 2007, you will receive a message from Excel, that the contents of the file do not match the file extension, because Excel "detects" this difference between what's inside the file and what the file extension is.


I think of PATH and GPATH as specifying the physical location of the CREATED files -- when they exist on my machine. Then, using URL=NONE instructs ODS to make me a relative &LT;IMG&GT; tag -- by not using any part of the physical name in the IMG tag. So, when I move both files (the HTML and the PNG) to the web server machine or network machine, they can be loaded correctly.

If you are using a GPATH= option and if it points to the same location as the PATH= option, and you want a relative tag, then (URL=NONE) is the way to make it happen. On the other hand, if, as frequently happens on web sites, the HTML files are physically separated from the image files, then you use URL= to specify the relative subdirectory (such as .\images\) which should be put into the relative tag. If you needed an absolute URL for the &LT;IMG&GT; tag, then you could do:
[pre]
GPATH="c:\temp\images" (url="http://myserv.com/main/images/")
[/pre]

... but I never code absolute URLs for IMG tags without checking with my webmaster first. They frequently want to move stuff around without breaking links and relative links are better for that.

cynthia
N/A
Posts: 0

Re: excel file with gchart output to be properly displayed over the network

Hello Cynthia et.al.,

Thanks Cynthia, the code works fine however when i try to run the code through SAS Enterprise guide, I get output as blank more so i was lookin to export the output using the SAS EG export option. Let me if that can be done.

Regards and Many Thanks,
Suniel
SAS Super FREQ
Posts: 8,643

Re: excel file with gchart output to be properly displayed over the network

Hi:
I would suggest that you open a track with Tech Support. I suspect that the EG export capability is only meant to deal with tabular output.

If you create the HTML file and you can open the HTML file in a browser (outside of EG), and everything looks OK, then you might try to open Excel and directly open the file in Excel (using File --> Open) instead of doing an export from EG.

Sometimes, EG puts "extra" ODS HTML statements around code that you submit. This extra output can sometimes appear as blank if you have your own ODS HTML statements in the code. Generally, the way around the creation of a blank HTML file in EG is to precede your code with:
[pre]
ods _all_ close;

ods html file=....;
...rest of code...
ods html close;
[/pre]

If none of that solves your problem, then your best resource is to open a track with Tech Support. To send a question to Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem". Alternately, you can go directly to the Tech Support Problem Form here: http://support.sas.com/ctx/supportform/createForm

cynthia
Post a Question
Discussion Stats
  • 8 replies
  • 218 views
  • 0 likes
  • 4 in conversation