How do i get multiple data sets single excel sheets with auto filter on each data set

[nerdday]

Hello, SAS community,

I'm trying to export 4 datasets to single sheet, which is no problem.  The problem revolves around getting the auto filter on more than one of the outputs.  When using the autofilter option, it only applies it to at most just one of the data sets.  However to apply it to all 4 datasets.  Below is a rough layout of the code.

ods listing close;

  ods tagsets.excelxp file= "&output./&file_name..xls" style=normal

  options (orientation = "landscape"

           embedded_titles= "yes"

           embedded_footnotes= 'no'

           sheet_name = "&sheet_name."

           gridlines= "yes"

           thousands_separator = ","

           fittopage= "yes"

           sheet_interval= "NONE"

           merge_titles_footnotes= 'yes'          

           wraptext='yes'                                          

           autofilter='all'

           autofilter_table = '1'

           autofit_width='yes'                      

           orientation='landscape'                                           

           absolute_column_width='20'                                                                          

           row_heights='0,0,0,0,0,0,0');

.

.

.

proc report1

run;

.

.

.proc report4 

,

,

ods tagsets.excelxp  close;

I tried the following:

'1,2,3,4'

'1-4'

I even tried putting a different option statement in front of each proc report while keeping the tagsets open until the end.  However, even that did not put autofilters on more than one sheet.

Thanks for any help you could provide.

[Vince_SAS]

Can you do this manually in Excel?  I think that Excel only allows AutoFilters on a single "table".  Try it using the attached "AutoFilter.xlsx" file.

Vince DelGobbo

SAS R&D