@chowdhury wrote:
Hello ,
I haven been creating excel reports (Excel 2003 XML spreadsheet type) in SAS using ODS TAGSET.EXCELXP code for years. My company just upgraded all its computer to office 2016. After upgrading to office 2016 I found out that I can no longer edit any of the excel reports that I create with SAS. Upon investigating with my system admin, I have found out that due to some policy they have decided to block editing/saving of all excel files prior to office 2007 and the policy cannot be changed. Which means I can open and view the files in protected view but can not do anything else.
Upon hearing that, I have tried different options I have found on Google and SAS community sites. I have learned that using ODS EXCEL is an option but unfortunately for SAS 9.4 and onwards users. We just got base SAS 9.1 and sas EG 7.1 very recently. It is unlikely that my company would agree to upgrading to SAS 9.4 version any time soon.
Therefore I am desperately seeking advice if anyone know how to create Excel 2007 and later workbook templates using base SAS 9.1 or SAS EG 7.1? So far I have tried the following and the all create reports in Excel 2003 format :
ODS MSOFFICE2K FILE = *[produces in excel 2003 format]
ODS EXCEL FILE = *[not supported in SAS 9.1 so gives me an error]
ODS tagset.excelxp file =*[produces in excel 2003 format]
Looking forward to your replies.
Thank you,
Chowdhury Anwar
Reference the highlighted text: No it doesen't. It creates XML files which are text. Excel can interpret them into a useable form. If you don't believe me use NOTEPAD to open one of the files generated by tagsets.excelxp. The first (way too many ) lines in <style> </style> blocks set appearance options like fonts and colors but when you get to the data portion you will see stuff like:
<Row ss:AutoFitHeight="1" ss:Height="15">
<Cell ss:StyleID="data__l" ss:Index="1"><Data ss:Type="String">Adams</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="2"><Data ss:Type="Number">9.0</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="3"><Data ss:Type="Number">89</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="4"><Data ss:Type="Number">8</Data></Cell>
</Row>
Which Excel would render as a line in a table looking like:
Adams 9.0 89 8
These rows would appear within a <table> </table> and the first row block would have column headings if any.
So if you are able to open the file then you should be able to save as. You may need to talk to you IT about editing XML files, not "EXCEL 2007". However the Microsoft "XML Editor" is not what you want as it plays with the text and does not display tables or data in any way that I would want to actually work with.
Or Export to CSV, which means you won't have any formatting such as cell colors or text, and open those and reformat.
Or Try ODS RTF and open tables from RTF to Word and Paste to Excel.
Or as @Reeza indicated, tell Management that SAS 9.1 is contemporary with Microsoft Windows XP and is seriously out of date.
