DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 28

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

[ Edited ]

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.

SAS Super FREQ
Posts: 8,865

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

[ Edited ]

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;

Contributor
Posts: 28

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

Posted in reply to Cynthia_sas

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.  

Super User
Posts: 11,343

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

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)

Contributor
Posts: 28

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 459 views
  • 0 likes
  • 3 in conversation