Experimenting with ODS EXCEL to create spreadsheets from SAS

70

The ODS ExcelXP tagset has served us well over the years. It provides a reliable method to get formatted SAS output into Microsoft Excel workbooks, where the business world seems to like to live. And it's available in Base SAS, which means that you don't need SAS/ACCESS to PC Files and any extra setup to begin using it.

In SAS 9.4 Maintenance 1, ODS EXCEL was introduced as an experimental feature. Even though it still has the "experimental" label in the recently released SAS 9.4M2, I've found it to be a useful addition to the many ways I can create Excel content from SAS. ODS EXCEL offers a couple of big advantages over ExcelXP:

ODS EXCEL produces a native XLSX file. Users of the ExcelXP tagset know that Excel complains about the file format as you open it. And the XML Spreadsheet format that it uses is uncompressed, resulting in potentially very large files.

ODS EXCEL supports SAS graphics in the output. ExcelXP users have come up with creative methods to insert graphs "after the fact", but it's not as convenient as a "once and done" SAS program. With ODS EXCEL, graphics from SAS procedures are automatically included in output.

Syntax-wise, ODS EXCEL is similar to ODS TAGSETS.ExcelXP. Chevell Parker shares many of those details in his SAS Global Forum 2014 paper; in absence of official doc for this experimental feature, Chevell's paper is essential.

Let's dive into an example. The following program looks similar to how you might use the ExcelXP tagset, but can you see the key differences?

ods excel file="c:\projects\output\example.xlsx" 
 /* will apply an appearance style */
 style=pearl
 options(
  /* for multiple procs/sheet */
  sheet_interval="none" 
  /* name the sheet tab */
  sheet_name="CARS summary"
 );
 
/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~Cars Summary and Histogram";
 
/* tabular output */
proc means data=sashelp.cars;
var msrp invoice;
run;
 
/* and a graph */
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
histogram msrp;
run;
 
ods excel close;

Note how I've targeted an XLSX file directly, instead of going through an XML intermediary file to placate Excel with the file format. And I included a graph with no problem. Like other ODS output destinations, I can apply styles and special formatting as needed. Here's a screen shot of the resulting spreadsheet:

odsexcel

I have a few additional observations from playing with this feature (did I say it was experimental?):

  • ODS EXCEL doesn't perform well with large detailed output. That is, if I use ODS EXCEL and PROC PRINT a data set with lots of columns and many thousands of rows, it's going to be slow -- and might hit a wall with memory usage. For that use case, PROC EXPORT DBMS=XLSX works better (though that requires SAS/ACCESS to PC FILES).
  • ODS EXCEL overwrites any existing Excel file that you target. That means that you aren't going to use this method to poke new values into an existing spreadsheet, or add sheets to an existing workbook. Compare that to PROC EXPORT DBMS=XLSX, which allows you to update an existing workbook by targeting just one sheet.
  • My example code works perfectly on the SAS University Edition! Simply change the destination file to be a location that SAS can write to -- for example, file="/folders/myfolders/example.xlsx".

If you want to learn more about the new ODS destinations in SAS 9.4 (which include HTML5, EPUB, POWERPOINT, and EXCEL), watch my interview and demo with David Kelley, R&D manager for ODS at SAS:

Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies
He also hosts the SAS Tech Talk webcasts each year from SAS Global Forum, connecting viewers with smart people from SAS R&D and the impressive work that they do.

Related Posts

70 Comments

  1. I'm looking forward to installing SAS 9.4M2, and working with the latest experimental version of ODS EXCEL. I always felt that, of the four ODS tagsets (ExcelXP, MSOffice2K, MSOffice2K_x, and TableEditor) to create reports that you could open in Excel, the ExcelXP tagset was over-promoted.

    Last year, I compared the tagsets (omitting MSOffice2K, which, like ExcelXP, cannot imbed graphs) with the alternatives of simply using ODS HTML (and an xls file extension for the output) or DDE (Dynamic Data Exchange). When not using DDE, I had been frustrated to find that one or another feature I needed would be missing from the particular ODS tagset I was using at the time. If tagset A, then feature X was missing, if tagset B, then feature Y was missing, etc.

    The criteria I used were whether or not each of the five alternatives supports:

    Pivot Tables
    Autofit Columns
    Filters
    Freeze Panes
    Side-By-Size Elements (Tables and/or Graphs)
    Multi-Sheet Workbooks
    Graphs
    Titles

    (By “Titles” I mean title lines in a worksheet, which was impossible only for TableEditor.)

    For ExcelXP, the answer is No for four criteria, which is exceeded only by HTML.

    I was pleased to learn from Chevell Parker’s SAS Global Forum paper that the ODS Excel destination performs an automatic mapping of SAS formats to Excel formats. For all of the ODS tagsets, to get your data to be formatted in the style of your desired SAS format, it is necessary to go through extra rigmarole.

    If the production version of ODS EXCEL delivers on all of the criteria listed above, it will be a tremendous improvement on the current situation of multiple partial-solution tools.

    Until then, the only tool evaluated that delivers on all of the criteria is DDE. (To do create a Pivot Table with DDE requires you to first record an Excel macro. To create a Pivot Table with any ODS alternative requires use of the TableEditor tagset, which is best covered by Peter Fernwood in http://support.sas.com/resources/papers/proceedings14/1483-2014.pdf )

    I also uncovered some idiosyncratic limitations/behaviors for the tagsets. If really interested in learning more about these I hope soon-to-be-obsolete alternatives, you can request my slide presentation.

    Excel is a tool that any IT user has and knows how to use. Being able to deliver a full-function Excel workbook with SAS is a high-value capability. Your SAS deliverable can then be viewed, reformatted however the recipient prefers, and, if desired, used as a starting point for further exploration with a familiar tool.

    • Chris Hemedinger
      Chris Hemedinger on

      LeRoy, thanks for the comments.

      Because there are so many different ways to put data into Excel and pull data out, these "survey of methods" papers and posts are very useful. I don't expect ODS EXCEL to meet all of the criteria you laid out for total control of the spreadsheet creation and scripting -- it will be more like ExcelXP with the added advantages I highlighted. For complete control of SAS content in an Excel ecosystem, I always recommend the SAS Add-In for Microsoft Office. That tool allows you to work in Excel using scripting, native charts, pivot tables and the like -- all while pulling your best SAS content in exactly how you need it. I realize that the SAS Add-In isn't part of every SAS programmer's toolbox, but as part of SAS Office Analytics, it's growing much more prevalent.

  2. Prashant Chegoor on

    Chris,

    As i have mentioned in the Linked In Comments section to this Post and as suggested by One more SAS User, it would really be very very Useful if ODS EXCEL can be used to update an Exisitng Excel Tempate especially when the Excel template resides on the Unix Environment..Hopefully Chevell Parker will make a Note of this.

  3. Hi Chirs,

    I ran the proc means / histogram example in your blog post of August 29, 2014. When I opened the excel spreadsheet made by the example, excel did not complain which is great.

    However, I did find a problem with the excel spreadsheet. In excel I tried to be format the proc means output, but I could not do it. The problem is the individual stats for the msrp and invoice variables are written to the same cell. For example, there is only one cell for n stat from the two variables. Since there is only one cell, 8 and 42 that are written to cell.

    I ran all three of the ODS excel destination examples in Chevell Parker's paper SAS 177-2014. There was no doubling up of data in the cells in excel spreadsheets that I created. I am happy to report that the last example does have output from a proc reg and an ods graphic and there was no problem.

    What is the source of this selected doubling of data in excel spreadsheet cells?

    Thanks much
    Doug DeGrote

    • Chris Hemedinger
      Chris Hemedinger on

      Doug, I haven't yet looked into it -- but it could be just a matter of how the ODS output is organized, with a side effect that these two formatted values share a cell. ODS "report" destinations are organized to look good in the destination, and aren't necessarily organized to be used as data. For a pure data output, you could use ODS Select and OUTPUT statements (or simply PROC MEANS with an OUTPUT statement) and then PROC PRINT that data set to an ODS EXCEL destination.

  4. Hi Chris,

    I did successfully transfer the data to excel when I used a PROC MEANS with an OUTPUT statement and used a PROC PRINT that data set to an ODS EXCEL destination. Below is the code I used:

    proc means data=sashelp.cars;
    var msrp invoice;
    output  out=proc_means_output_1;
    run;
     
    proc transpose  data=proc_means_output_1 out=flipped;
       id _stat_;          /* variable names in flipped data set  */ 
      var MSRP Invoice;    /* put  data column  in incoming data set
                                to data in rows in flipped data set        */
    run;
    
    data flipped_2  ( Rename= (_NAME_=Variable));
       set flipped;
       /*  n variable fix: get rid of annoying dollar format   */
       format n 4.0;
    run;
    
    title1 "Proc Print based on Proc Means Output data set";
    proc print data=flipped_2   noobs;
    run;
    

  5. Hi Chirs,

    I also followed your suggestion that I use ODS Select and OUTPUT statements.

    I interpreted your suggestion by adding the two lines of ODS code to the proc means within the ODS excel file=... and ODS excel close grouping of code. Below is my code with the extra two lines of ODS code.

    proc means data=sashelp.cars;
    
    var msrp invoice;
    
    ods select summary;
    ods output summary=Proc_means_output_2;
    
    run;
    

    The results of the Ods select Summary statement was the same as before, information for both variables were placed in the same cell.

    I used a proc print to list the contents of Proc_means_output_2 file. The result of the ODS output / Proc Print coding produced a table with one observation instead of two observations of data. The one observation table has all the data but the table has the data in a format is not ready for prime time.

    proc print data=Proc_means_output_2   noobs;
    run;
    

    Thanks much,
    Doug DeGrote

  6. Pingback: Using ODS EXCEL and PROC EXPORT to bundle Excel-based reports - The SAS Dummy

  7. The advantage of Excel controls in Base SAS vs Add-In for MS Office is that Excel reports can be generated offline in batch mode and delivered to business staff who do not have Add-In for MS Office installed.

    • Chris Hemedinger
      Chris Hemedinger on

      Rod, yes -- PROC EXPORT and ODS EXCEL and other SAS code approaches are perfect for batch operations. However, you can script/automate SAS Add-In operations too! See this terrific paper by Tim Beese that shows you how. While you need to have the SAS Add-In in order to run the script, the consumers of the Excel content do not need the SAS Add-In for Microsoft Office in order to view/work with the result.

  8. Pingback: Don’t panic: maybe your XLSX file is already opened - The SAS Dummy

  9. Hi Chris

    I have been experimenting a little as to find an DDE alternative using the START_AT option. However this doesn’t work for me:

    ods excel file="h:\odsexceltest.xlsx" options(sheet_name="Testing");
       ods excel options(start_at="A1");
       ods text="We are at A1";
       ods excel options(start_at="B2");
       ods text="We are at B2";
    ods excel close;

    Expected: A1: We are at A1
              B2: We are at B2
    Observed: B2: We are at A1
              B3: We are at B2 
    

    Is it supposed to work this way? I would be ever so happy if SAS Institute would give it a thought.

    Regards, Peter

    • Chris Hemedinger
      Chris Hemedinger on

      Peter, thanks for the comment. Currently, the START_AT option does not allow you to modify the "output position" in mid-sheet. I expect that what you are observing is ODS EXCEL respecting only the last START_AT option that you specify.

      I have seen this request come in just recently -- perhaps from YOU if you have already been talking to SAS about it. Current thinking is that this is very difficult to accommodate when rendering the XLSX format due to the internal structure of the file. DDE works because it's not worried about file format: it's sending direct commands to the Excel application to move the cursor around.

      • Peter Kellberg on

        Hi Chris
        Thanks for answering so promptly ;-) Yes - the last START_AT wins. And yes, DDE works nicely if you have Excel on the SAS-server (or pc with SAS installed on it). In Statistics Denmark we currently use (via RDP) EG and Classic SAS installed on Windows servers. This in fact works pretty well (and eliminates the local workstation installs). But we don’t want to clutter up the SAS servers with Excel. So for now the DDE users have to look for alternative solutions. For a short moment I thought I had won the lottery ;-)
        An idea: How about a PROC EXCELWRITER that reads a SAS dataset with two columns POS and VALUE (maybe also CELLFORMAT).PROC EXCELWRITER then updates the internal XML-files of an XLSX file (in reality a ZIP file that SAS can read). No Excel to be involved here. proc excelwriter data=excelupdates file="h:\MyExcelFile.xlsx";cellposvar=pos;cellvaluevar=value;cellformatvar=cellformat;run;I know it’s not as easy as it looks at first glance due to the internal XML file relationships. Oh yes and the risk of future changes the internal structure of the XLSX file.
        Regards,
        Peter

  10. Hi Chris,

    I wanted to output two data sets generated using proc report on the same page . Table 1 first and followed by table2 below it . How do I achieve this using ODS TagSets excel xp. I'm able to output each table in different tabs in the excel spreadsheet but not one below the other on the same tab.

    I want to output two different reports created using proc report and output them into excel using one below the other using ODS TAG SETS excel xp.

  11. Hi Chris,
    I created excel spreadsheet with 3 proc reports on one tab using sheet_interval= 'none', next step is to create exact same output for 36 different cities. In order to achieve this I created macro
    with Proc report which gets exported to excel using ODSTagsets. I have to repeat this process 36 different times and all the output need to be in the same excel spreadsheet, but in different tabs. When I run the macro within ODS facility it overwrites the same excel spreadsheet. Is there a way to achieve this.

    Thanks a bunch!

    • Richard DeVenezia on

      Use the sheet_name option to start a new worksheet (aka tab) in the current work book

      ODS EXCEL options (sheet_name="&city_name");

  12. I figured it out creating a macro within a macro creates outputs multiple tables with sheet_interval= 'none' and creating a macro within ODS facility outputs multiple tables in different tabs in excel spreadsheet multiple tabs in excel spreadsheet.

    Thanks!

  13. Pingback: Using Lua within your SAS programs - The SAS Dummy

  14. Bjorn Jorgensen on

    Hi.

    i have a problem With ods Excel.
    i get this error Message:
    ERROR: Unable to load module 'SpreadsheetML' from template store!
    ERROR: No body file. EXCEL output will not be created.

    What is this? i've googled and searched for it, but no luck so far.

    I am currently using ods tagset.excelxp With proc report to create the spreadsheets.
    I really don't want to have xml files, i want the output to be normal Excel files.

    • Chris Hemedinger
      Chris Hemedinger on

      Hi Bjorn, Are you sure you're using ODS EXCEL statements -- with SAS 9.4 Maint 2 or Maint 3? If so, you might need to contact Technical Support about this.

      • Hi all, is there anyway that we can use excelxp to output results into multiple workbooks,and each work book containing multiple sheets in them?

      • Bjorn Jorgensen on

        I'm sorry, I forgot to mention I am using SAS EG 5.1.

        This is my basic code for ODS Excel:

        ods excel file="H:\terminkjoring\F&f_nr._&ar_input.&termin_input..xls" style=statistical options (suppress_bylines='yes'); ods excel options(sheet_interval='none' sheet_name="&fylkenavn2."); proc report creates output here: run; ods _ALL_ CLOSE;

        From what i have found ODS and proc report is the most suitable for formatting output the way i want. proc export works, but it just creates tables without any formatting.
        Is there another way to create Excel files that allows me to format the output like ods and proc report?

        • Chris Hemedinger
          Chris Hemedinger on

          Bjorn,

          That code should work, but it would require SAS 9.4 Maint 2 or 3. If you're using SAS Enterprise Guide 5.1, you might be still on SAS 9.3. (SAS Enterprise Guide 5.1 would work with 9.4, but I would expect you to be moved to 6.1 or 7.1 if you had SAS 9.4.)

          Otherwise, you would have to use ODS TAGSETS.EXCELXP -- the syntax is similar but the output is an Office XML file, and not a native XLSX file.

          • yes, we are still using 9.3.
            Guess i am stuck with tagset.excelxp for now then.

            i was thinking of importing the xml file and using proc export to get it to native Excel, but i Guess the formating will be gone?

          • Chris Hemedinger
            Chris Hemedinger on

            Yes, that's right -- Once you export as data, then the spreadsheet arrangement (display formatting from ODS) is gone.

  15. Hi ,

    I have downloaded the tableeditor and am trying to run the code for pivot table but its throwing me errors like 1) ERROR: Insufficient authorization to access /sas/config/Lev1/SASApp/C:\Users\pmuppidi\MySAS\Sample1.xml.
    ERROR: No body file. TAGSETS.EXCELXP output will not be created.
    2)ERROR 13-12: Unrecognized SAS option name NOXSYNC.

    Please some one assist me in getting this fixed . Code is below :

    ods tagsets.ExcelXP file="C:\Users\pmuppidi\MySAS\Sample1.xml" options(sheet_name="Sheet1");

    proc print data=Sample1;
    run;
    ods tagsets.Excelxp close;
    /* Create and execute the script file that is generated */
    /* with the TableEditor tagset. */
    ods noresults;
    options noxwait noxsync;
    ods tagsets.tableeditor file="C:\Users\pmuppidi\Downloads\tableeditor\tableeditor.tpl"
    options(update_target="C:\\Users\\pmuppidi\\My SAS\\Sample1.xml" open_excel="no" output_type="script"
    sheet_name="Sheet1" pivotdata="quantity,quantity,profit,profit"
    pivotdata_stats="sum,sum,sum,sum" pivotcalc="none,total,none,total"
    pivotpage="Mail type,Mail id " pivotrow="New,Repeat"
    /*pivotdata_caption="Sum of Quan,% of,Sum of Prof,% of"
    pivotdata_tocolumns="yes");*/
    data _null_;
    file print;
    put "Sample1";
    run;
    ods tagsets.tableeditor close;
    x "cscript 'C:\Users\pmuppidi\Downloads\tableeditor\tableeditor.tpl'";

    • Chris Hemedinger
      Chris Hemedinger on

      From the paths in the log, it looks like you're running on a UNIX (or Linux) version of SAS, but your code references files on your local Windows machine. If using SAS Enterprise Guide with a remote SAS session, you'll need to change those paths to a folder you can write to on UNIX (ex: /u/pmuppidi). If using SAS University Edition, you'll have to reference the shared folder between the SAS session and your Windows machine.

    • Navin Salian on

      Hi,

      I am using the below code to generate the pivot on UNIX via JavaScript. However the option noxsync noxwait; both are invalid option and I am not able to see the final pivot an chart output. I am using SAS9.4 version on UNIX. Kindly let me know if I am missing anything. Thanks for the help!

      options noxsync noxwait;
      ods tagsets.tableeditor file="/apc/analyst/abdfc/temp/PivotChart.js"
      /* remember to escape the backslashes */
      options(update_target="/apc/analyst/abdfc/temp/temp.xlsx"
      output_type="script"
      sheet_name="shoe_report"
      pivot_sheet_name="Profit Analysis Pivot"
      pivotrow="region"
      pivotcol="product"
      pivotdata="sales"
      pivotdata_fmt="$#,###"
      pivot_title="Pivot Analysis for XXX"
      pivot_format="light1"
      pivotcharts="yes"
      pivot_chart_name="Profit Analysis Charts"
      chart_type="columnclustered"
      );

      /* dummy output to trigger the file creation */
      data _null_;
      file print;
      put "test";
      run;

      ods tagsets.tableeditor close;

      X '/apc/analyst/abdfc/temp/PivotChart.js';

      • Chris Hemedinger
        Chris Hemedinger on

        Hi, this approach won't work on a UNIX machine, where the script is used as a post-process step. The script automates Microsoft Excel, which has to be running on a local Windows machine. You can use Linux SAS to generate the initial XLSX file and the script, but the two need to end up on a Windows machine where you then run the script.

  16. Hi,
    I am using ODS EXCEL / ODS tagsets.excelxp to generate .xlsx output using SAS.
    I need to present Column Grouping in the Final Excel output.
    In Excel, I would do it as -
    1. Select desired columns (side by side)
    2. Goto Menu -> Data -> Group -> Group
    This gives me a horizontal line above these columns with a "-" sign. Using this sign, I can hide / unhide these grouped columns.
    Now, I want to have same output using SAS.
    Can anybody suggest how to do it? I am using SAS Studio currently.

    Thanks in advance for your help.

    Thanks,
    Ajay.

  17. Andrea Zimmerman on

    I'm having some issues with this. I'd like to direct some output to one tab, then move to another tab.

    On my PC, running 9.4 Service Pack 1 this code produces exactly what I expect, two tabs, one with the rows of females and the other with the rows of males.

    ods excel file="&path./excel.xlsx";

    ods excel options(sheet_interval='none' sheet_name="Females");
    proc print data=sashelp.class;
    where sex="F";
    run;

    ods excel options(sheet_interval='none' sheet_name="Males");
    proc print data=sashelp.class;
    where sex="M";
    run;

    ods excel close;

    ods tagsets.excelxp file="&path./tags.xls";

    ods tagsets.excelxp options(sheet_interval='none' sheet_name="Females");
    proc print data=sashelp.class;
    where sex="F";
    run;

    ods tagsets.excelxp options(sheet_interval='none' sheet_name="Males");
    proc print data=sashelp.class;
    where sex="M";
    run;

    ods _all_ close;

    But when I run the same thing on our UNIX box running SAS 9.4 with the latest Service Pack, I end up with just what I expect from tags.xls, but on excel.xlsx I get one tab called Females and both tables are printed there.

    • Andrea Zimmerman on

      And of course it isn't this simple. I need the ODS Excel destination because I'm really doing graphs and I need the latest service pack because the graphs I'm doing aren't supported in SP1.

      • Andrea Zimmerman on

        Ok, got an answer from SAS tech support. It is an issue with SP3 that will hopefully be fixed with SP4. You can put everything on one tab, or one thing per tab, but if you want a couple things on one tab, and a couple things on the next tab, you have to trick SAS into creating the new tab.

        ods excel file="C:\elever.xlsx";
        ods excel options(sheet_name="SkoleElever" sheet_interval="none");

        proc print data=sashelp.class;
        run;

        proc print data=sashelp.class;
        run;

        /* Add dummy table */

        ods excel options(sheet_interval="table");
        ods exclude all;

        data _null_;
        file print;
        put _all_;
        run;
        ods select all;

        ods excel options(sheet_interval="none");
        proc tabulate data=sashelp.class;

        class age sex;
        table age, sex;
        run;

        proc print data=sashelp.class;
        where age=12;
        run;
        ods EXCEL close;

  18. Greetings,
    I am using ODS EXCEL to generate .xlsx output using SAS EG, transitioning from PC SAS to a .rtf file. Can you advise how best to add a .jpg to a footer to the .xlsx output? My old code is '^S={PREIMAGE="Location\Picture.JPG"}';. Thank you, Robyn

  19. Hello Chris,

    Does ODS EXCEL have the possibility of creating a data validation list just like in Excel (drop down menu with hardcoded values to choose from )?

    Thanks

    • Chris Hemedinger
      Chris Hemedinger on

      No, not directly. You could set something up with post-processing -- running a VBA script to grab a range and assign a validation list.

  20. Hi Chris,

    Can u show a code using ods tagsets to generate an excel file with merged cells? mergeAcross wont work. Thank you in advance.

    ex:
    -from a sas table:
    ----------------------------------------------------- | 1 | | ----------------------------------------------------- | 2 | 3 | -----------------------------------------------------
    -to an excel file:
    ----------------------------------------------------- | 1 | ----------------------------------------------------- | 2 | 3 | -----------------------------------------------------
    (for a better illustration)

    -from a sas table:
    ----------- | 1 | | ----------- | 2 | 3 | -----------
    -to an excel file:

    ----------- | 1 | ----------- | 2 | 3 | -----------

  21. Hi Chris,
    I run the code in your blog and I did got a xlsx file, but it's not perfect! I run on SAS 9.4 TS1M2. The two variables MSRP and invoice and their corresponding stats are in the same cells.

    • Chris Hemedinger
      Chris Hemedinger on

      ODS EXCEL was declared "production" in 9.4 TS1M3, so this issue would be fixed there. You could try with the free SAS University Edition to see if your test case works.

  22. Hi,

    I am trying to convince the tech-people here that some new EG 7.1 features are not yet working.
    Especially ODS excel. You mention "9.4 TS1M3", is that the same as our &SYSVLONG=
    9.04.01M3P062415 ?

    • Chris Hemedinger
      Chris Hemedinger on

      That's the correct version of SAS. Now, you have to check your version of SAS Enterprise Guide. v7.1, 7.11, and 7.12 are all part of the "7.1" family, but some new features are present only in 7.11 or 7.12.

  23. bjorn jorgensen on

    i used ods tagsets.excelxp before to make Excel-files, and used proc report to print to this Excel file. the first line in this Excel file was made by #BYVAL and it worked With Excelxp.

    now i changed my code to use ods Excel because i wanted native Excel files. but now the proc report won't resolve #BYVAL like it did before.

    this is the code that creates the Excel-file, the options are similar for Excelxp and Excel for this part :
    ods Excel/tagsets.excelxp file="...\F&f_nr._&ar_input.&termin_input..xls"
    style=statistical
    options (suppress_bylines='yes');

    this is the code before With ods tagset.excelxp:
    ods tagsets.ExcelXP options(sheet_interval='byfullknavn' sheet_name='#BYVAL(fullknavn)');

    proc report data=kommuner_&f_nr. headline headskip nowindows noalias;
    column ("#BYVAL(fullknavn)" "&ar_input." '--' );
    by fullknavn;

    this is the code With ods Excel:
    ods excel options(sheet_interval='bygroup' sheet_name='#BYVAL(fullknavn)');

    proc report data=kommuner_&f_nr. headline headskip nowindows noalias;
    column ("#BYVAL(fullknavn)" "&ar_input." '--');
    by fullknavn;

    any ideas why ods Excel won't resolve that #BYVAL for the column and ods tagset.excelxp does?

    only difference is the ods option sheet_interval.

  24. Hi Chris,
    I have created some bar charts by using gchart in SAS EG7.1. Now I want to export these vertical bar charts to excel. Also I need to put two graphs (Bar charts) side by side. Could you please provide me a sample code to do this. Waiting for the reply. Thanks in advance

      • Thanks a lot. Now I have exported bar charts in excel but I don't want the table and description of graph that is displaying along with the charts. Also could I change the format in saved excel & if I run again the same code, does that format of the chart remains as I changed or should I do this in code only?

        • Chris Hemedinger
          Chris Hemedinger on

          Unless you're using the SAS Add-In for Microsoft Office (which runs the SAS code for you and pulls the results into Excel), your formatting changes will not be retained when you re-run code. If you are using the SAS Add-In -- then yes! It works.

          If you want to remove the graph title from the image (if it's embedded), make sure that the NOGTITLE and NOGFOOTNOTE options are specified on your ODS statement.

  25. Pingback: Send your SAS graphs to Excel, directly to Excel ... - SAS Learning Post

  26. Hi Chris,
    I am using SAS EG 4.1 and report graph format is "Active X". I am trying to export my graph to excel using ODS Excel. But the GRAPH doesn't get exported to excel and the page is empty.

    If change the graph format to "JPEG" its working but the graph is not looks good.
    It could be great help if you advise me on this.

    Senthil

    • Chris Hemedinger
      Chris Hemedinger on

      EG 4.1? So you're using SAS 9.1? JPEG graphs look much better in later versions of SAS. You might try using the ActiveX Image or Java Image format instead, which produces a graph that looks like ActiveX but isn't interactive.

      • Hi Chris,
        Thanks for your response.

        I have created charts using SAS EG 7.1. The below code I was using to create graphs. The Graph is not copied to excel if I set graph format to "ActiveX/Java" and graph is not clear as expected if I change to "JPEG" graph format.

        Appreciate if you suggest any other alternate for this. Thanks.

        libname mylib 'M:\SG\MV\retail\data\sandbox';
        ods excel file="M:\SG\MV\Test2.xlsx"
        ;

        data anno1;
        length function style color $8 text $20;
        retain xsys '5' ysys '5' when 'a' color 'white';
        function='move';x=48;y=2.2;line =1;output;
        function='draw';x=50;y=2.2;line=1;output;
        function='label';color ='black';x=43.3;y=2.2;position='6';size=.9;text="KS";output;
        function='move';x=55.5;y=2.2;line =37;output;
        function='draw';x=57;y=2.2;line=37;output;
        function='move';color='black';x=38.7;y=1;size=.9;line =1;output;
        function='symbol';color='white';x=64.5;y=0;position='6';size=3;text="cube";line =0;output;
        function='label';color ='black';x=55;y=2.2;position='6';size=.9;text="Accuracy Ratio";output;
        run;
        goptions cback= white;
        symbol1 i=hilot v=none c=black h=0 W=0;
        symbol2 i=none v=cube c=red h=0 w=0 pointlabel=none ;
        symbol3 i=JOIN h=9pt v=DOT CV=blue CO=blue LINE=1 w=1 CI=blue pointlabel=none;
        symbol4 i=JOIN h=5pt v=NONE CV=black CO=black LINE=37 w=2 CI=black pointlabel=none;

        Legend1
        LABEL=( FONT='Arial' HEIGHT=8pt JUSTIFY=center " ")
        VALUE=(FONT='Arial' HEIGHT=8pt "Threshold" " " " " " " );
        ;
        PROC GPLOT DATA=mylib.test ;
        PLOT
        BOUND1*calc_poolv
        AR*calc_poolv
        KS * calc_poolv
        tresh*calc_poolv
        / overlay haxis=axis2 vaxis=axis1 LEGEND=LEGEND1 anno=anno1;
        ;

        ;RUN;

        ods excel close;

        • Chris Hemedinger
          Chris Hemedinger on

          You could try ActXImg or JavaImg to create a graph that looks like the ActiveX result, but won't be interactive. Also, you might try using PROC SGPLOT instead; I think you can achieve the same graphical result with less code.

  27. Pingback: Using SAS to add PivotTables to your Excel workbook - SAS Users

  28. Pingback: Using LIBNAME XLSX to read and write Excel files - The SAS Dummy

  29. Gerry Skurski on

    Hi Chris,

    I'm just getting into ODS Excel (versus excelXP tagsets) and I'm finding that in my company's implementation of SAS 9.4/SAS EG 7.12 HF1 (7.100.2.3362) (64-bit), I get the following error while trying to submit your example code:

    ERROR: ZIP entry duplicate name: _rels/.rels.

    Do you know what might be causing this?

    Thanks!
    Gerry

    • Chris Hemedinger
      Chris Hemedinger on

      From what I've read, this can happen if you have multiple ODS EXCEL destinations active that aren't discerned by using the ID= (Ex: ODS EXCEL(id=excel1)...)

      What specific version of SAS 9.4 do you have (maint level)? Some of this might have been addressed in Maint 3 or Maint 4.

      • Gerry Skurski on

        It looks like we're still on Maint 1, so I'll pass that info along to our IT department and hopefully they can get that updated. In the meantime, adding an ID to the ODS statement got rid of the error perfectly. Thanks for your help!

  30. We will FINALLY be going to 9.4 in the next few weeks! Horray!! In the meantime I am curious to know if graphs produced by ODS Excel would be editable by excel users. Are they indistinguishable from native Excel charts?
    Could you email me the output of that code? Thank you.

    • Chris Hemedinger
      Chris Hemedinger on

      Jeff,

      The graphs created by SGPLOT are not native Excel charts, they are static images (usually PNGs) created by SAS. However...if you have the SAS Add-In for Microsoft Office, you can create native charts using the graph tasks in the SAS menu -- reporting on SAS data. There was, for a short time, an experimental PROC MSCHART that could create native charts in Excel, but that is not available in the most recent releases.

Leave A Reply

Back to Top