BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bnarang
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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
Quartz | Level 8

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

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
  • 2 replies
  • 7313 views
  • 4 likes
  • 3 in conversation