SAS graph/Charts to excel output

[bnarang]

Hi Everyone

I am not sure if this question was asked before in this forum, Apologies.

My purpose is basically to output the sas graphs for example scatter plots, gcharts to excel in designated cells. Is it possible?

[Cynthia_sas · Accepted solution]

Hi:

  Using HTML-based methods, it is possible to get SAS/GRAPH or ODS GRAPHICS output into an HTML file and then you can open that HTML file with Excel. That does not, however, give you "cell level" control of the placement of the graph. The ODS MSOFFICE2K destination creates a Microsoft-friendly HTML file, because it creates Microsoft HTML markup tags.

  If you use the SAS Add-in for Microsoft Office with a Stored Process, you can control the cell in which the stored process output should be inserted. This generally is in A1 by default, but you can change that. Of course, using a Stored Process requires the BI Platform or the Office Analytics Platform, so this may not be a choice for you.

  If you have both SAS and Excel on the same machine, I believe you can use DDE for this.

  An example of an ODS MSOFFICE2K method is shown below. Note that one example uses SAS/GRAPH and the other example uses ODS GRAPHICS. The file -is- an HTML file, which uses an <IMG> tag to point to the location of the image created by the graph procedure. The output file is named with .XLS as a convenience in order to launch Excel from the SAS Results Viewer. When Excel complains that you gave an HTML file the XLS extension, you just have to click YES to continue opening the file.

   If you use the file extension of .HTML, then you would have to launch Excel manually and do a File--> Open. I find that using ODS MSOFFICE2K, I may still have to go in and "tweak" the placement of the graph. Sometimes Excel will float the graph on top of my output. Or, if I have 2 graph steps, Excel will put one image overlapping the first image. This is due to how Excel deals with the <IMG> tag in the HTML.

cynthia

ods _all_ close;
ods msoffice2k path='c:\temp' (url=none)
         gpath='c:\temp' (url=none)
         file='open_with_Excel1.xls'
         style=seaside;
      
title 'A Report1';
proc print data=sashelp.class;
run;
     
title 'SAS/Graph';
proc gchart data=sashelp.class;
  vbar age / sumvar=height type=mean;
run;
quit;
ods msoffice2k close;

  
ods msoffice2k path='c:\temp' (url=none)
         gpath='c:\temp' (url=none)
         file='open_with_Excel2.xls'
         style=seaside;
          
title 'A Report2';
proc print data=sashelp.class;
run;

  
title 'ODS GRAPHICS';
proc sgplot data=sashelp.class;
  vbar age / response=weight stat=mean;
run;
ods msoffice2k close;

[Vince28_Statcan]

You can use DDE to copy a SAS/GRAPH output to an excel cell. You will need to know the full extension of the graph and the name of the excel workbook if it already exists.

If you take this approach, you should do some forums/sas website searches for DDE and do some reading. It also requires excel to be installed on the server where the sas program is run so if it's your own computer, it's no problem but if it's on a DB server, it may not be a viable solution. I hope I didn't do any syntax error when retyping this from SAS.

options noxwait noxsync;

x '"C:\Program Files\Microsoft Office\Office12\EXCEL.exe"';

data _null_;

     z=sleep(5); /* 5seconds sleep, needs to be long enough for excel to open */

run;

filename DDEcmds dde "excel|system";

data _null_;

     file ddecmds;

     put '[open("C:\testexcelfile.xlsx")]';

     put '[workbook.activate("Sheet1")]';

     put '[select("R4C12")]';

     put '[insert.picture("C:\users\public\pictures\sample pictures\forest.jpg")]';

     put '[save()]';

     put '[error("false")]'

     put '[quit()]';

run;

options xwait xsync;

  Vincent