autofilter

[Chip]

We have version 8.2; ancient I know. We would like to create an excel spreadsheet with the autofilter set to on. I can't seem to find an example outside of the excelxp tagset (which I don't think I can use in 8.2). I am using htmlcss as my parent tagset. Any help is greatly appreciated. Thanks!

[Cynthia_sas]

Hi:
The issue is that in SAS 8.2, the only way to create files for Excel to open were:
1) use ODS CSV or CSVALL -- which definitely did not have a way to set autofiltering when the CSV file was opened and rendered and
2) use ODS HTML or ODS HTMLCSS or other HTML-based destinations to create an HTML file for Excel to open and render. In order for you to put autofiltering into an HTML file, Microsoft had to allow autofiltering in HTML tags (which I do NOT know whether this is even possible.)

The reason that you can set autofiltering to ON for TAGSETS.EXCELXP output is that ODS TAGSETS.EXCELXP conforms to the Microsoft Spreadsheet Markup Language XML specification for describing WORKBOOKS and WORKSHEETS -- so they surfaced an XML tag to allow the autofiltering to be turned on via XML.

Since the HTML tags are primarily meant to be rendered in a browser and since being able to open an HTML file with Excel is a secondary feature of the HTML, I doubt that there is a way to do autofiltering in an HTML file -- this would require more research into Microsoft HTML.

When I go into Excel and manually create a worksheet by typing in some headers and numbers and then put autofiltering ON, the worksheet shows autofiltering. However, if I save the file as an HTML file (whether a MHT or HTML file), I get a warning from Excel that some features will be lost. And if I click on the Help button for this message, it says that for:

"Web Page and Single File Web Page
These Web Page file formats (.htm, .html), Single File Web Page file formats (.mht, .mhtml) can be used for exporting Excel data. In Office Excel 2007, worksheet features (such as formulas, charts, PivotTables, and Visual Basic for Application (VBA) projects) are no longer supported in these file formats, and they will be lost when you open a file in this file format again in Excel."


This is why I believe it is not possible to create autofiltering with SAS in an HTML file -- it has nothing to do with the version of SAS - -but with the limitations of what you can do with HTML that is going to be opened with Excel.

Of course the "fix" for it might be to write an Excel macro or VBA script that you could apply once the HTML file was opened. Or just bite the bullet and get SAS 9. I believe that if you used DDE, ODBC or OLEDB methods of populating a worksheet with SAS, you might be able to turn on autofiltering....but this is not my area of expertise, so you might need to work with someone who knows DDE or OLEDB to figure this out. In SAS 8, I used to have students who created CSV files and then they somehow pulled those CSV files into an Excel template (.XLT file) using VB, (I think it was VB but they could have said VBA -- I'm not quite sure of the difference between them).

cynthia

[Chip]

Unfortunately that bullet isn't mine to bite. 😞 Thanks for the info! That is very helpful.

[sbb]

Your enterprise/organization really does need to approach that bullet ASAP, if you intend to continue being supported and to develop (with forward thinking) using the SAS system.

For the sake of counter-whining, consider that you are paying for those SAS architecture improvements, while not being able to take advantage. To say that it's not yours to decide is a cop-out and I'd submit that it is your role to make the case that "upgrading to a supported SAS version is the right approach" given what you're missing.

Scott Barry
SBBWorks, Inc.

[Chip]

Mr. Barry,

Wow, that's a bit presumptuous. How do you know I haven't made the case? I don't think assailing someone's character is appropriate in this forum, especially coming from a position of ignorance.

[sbb]

I apologize if you consider the reply to be personally directed. Hopefully you will share the feedback with others who can help make a mgmt decision regarding SAS. Good luck.

Scott Barry
SBBWorks, Inc.