topic Re: Creating excel 2007 and onward report using base sas 9.1 and SAS EG 7.1 in ODS and Base Reporting

Creating excel 2007 and onward report using base sas 9.1 and SAS EG 7.1

chowdhury — Mon, 10 Sep 2018 15:13:22 GMT

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

Re: Creating excel 2007 and onward report using base sas 9.1 and SAS EG 7.1

Reeza — Mon, 10 Sep 2018 15:18:00 GMT

We just got base SAS 9.1 and sas EG 7.1 very recently.

That doesn't quite make sense to me, why would a company purchase and install a version of software that's beyond a decade old already at this point?

Double check your version using the following code:

proc product_status;run;

If you're not sure how to interpret it please post it here - there should be nothing confidential in it.

Mine looks like:

For Base SAS Software ...
Custom version information: 9.4_M5
Image version information: 9.04.01M5P090617
For SAS/STAT ...
Custom version information: 14.3
For SAS/GRAPH ...
Custom version information: 9.4_M5
For SAS/ETS ...
Custom version information: 14.3
For SAS/OR ...
Custom version information: 14.3
Image version information: 9.04.01M5P110817
For SAS/AF ...
Custom version information: 9.4_M5
For SAS/IML ...
Custom version information: 14.3
For SAS/QC ...
Custom version information: 14.3
For SAS/SHARE ...
Custom version information: 9.4_M4
For High Performance Suite ...
Custom version information: 2.2_M6
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M5
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M5

@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

Re: Creating excel 2007 and onward report using base sas 9.1 and SAS EG 7.1

ballardw — Mon, 10 Sep 2018 16:23:02 GMT

@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.

Re: Creating excel 2007 and onward report using base sas 9.1 and SAS EG 7.1

Cynthia_sas — Mon, 10 Sep 2018 18:05:37 GMT

Hi:
To clarify:
ODS MSOFFICE2K makes Microsoft Office 2000 HTML compliant files and the correct file extension for this file is .HTML or .HTM
ODS TAGSET.EXCELXP makes an XML file that is ASCII text file and the file conforms to the Office 2003 Spreadsheet Markup Language XML specification -- the correct file extension for this file is .XML
ODS EXCEL creates a compressed XML file that conforms of the Office Open XML Standard of 2007 (for .XLSX files) -- the correct file extension for this type of file is XLSX.

Cynthia