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

nerdday — Fri, 30 Nov 2018 04:39:05 GMT

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.

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

Vince_SAS — Thu, 14 Mar 2019 15:42:37 GMT

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

topic How do i get multiple data sets single excel sheets with auto filter on each data set in ODS and Base Reporting

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

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