topic Re: Add an autofilter to an existing excel xlsx file stored on unix server in SAS Programming

Add an autofilter to an existing excel xlsx file stored on unix server

Bryan — Thu, 20 Oct 2016 16:59:02 GMT

Writing out ~20 excel xlsx files using proc export since number of rows ~100,000+ per file. Cannot seem to find a way to open up the file after the proc export to add autofilters to the excel xlsx file. Running SAS 9.4 m2. Thanks for your help in advance.

Re: Add an autofilter to an existing excel xlsx file stored on unix server

Cynthia_sas — Thu, 20 Oct 2016 17:44:24 GMT

If you use ODS TAGSETS.EXCELXP or ODS EXCEL, then there is an autofilter option. But with PROC EXPORT, you have to go into the file after the fact.

Here's an example of creating an autofilter instruction in the file using ODS.
cynthia

ods tagsets.excelxp file='c:\temp\usefilter.xml'
options(autofilter='on') style=htmlblue;
ods excel file='c:\temp\usefilter.xlsx'
options(autofilter='on') style=htmlblue;
proc report data=sashelp.class ;
run;
ods tagsets.excelxp close;
ods excel close;

Re: Add an autofilter to an existing excel xlsx file stored on unix server

Bryan — Thu, 20 Oct 2016 19:00:10 GMT

Thank you. I looked into those options but I should have stated that the excel files have pre-built pivot tables, a different pivot selection for each excel file. I am refreshing the data with proc export. For tagsets, the file would have to be an xml file and ods excel creates a new file.

Re: Add an autofilter to an existing excel xlsx file stored on unix server

ballardw — Thu, 20 Oct 2016 19:34:07 GMT

Since you obviously are not going to be able to process all of the data in Excel why are you worried about any process in that application?

What do you do with the 20 exported files? If you want to look at records related to a specific value(s) of a column you need to repeat the step in each of 20 files. It seems like it would make more sense to organize likely reports in SAS and then look at those.

(Having had to talk people of working like this when Excel was limited to 64K rows)

Re: Add an autofilter to an existing excel xlsx file stored on unix server

Bryan — Wed, 26 Oct 2016 16:43:55 GMT

Thanks for your input, excel can handle the seperate files that are being built by the SAS process. The analysts double click on the pivot table to pull up records that they are interested in or need to investigate. Agree if I could just provide a SAS report it would be much easier.