ODS Excel Multiple Sheets AutoFilter

[matt_H]

Good afternoon-

My first post in the community and a SAS novice so please excuse any lapses in etiquette.

I'm trying to get accustomed to the ODS Excel system and having an issue.

What I'm trying to do is output 3 tables with text onto one Excel sheet, then output another table onto the second sheet.

I'd like to have autofilters turned on in the second sheet.  The problem is that the filters are showing up in row 6 of the second sheet, as opposed to row 1 where the headers are.  I believe this is because the first sheet has 5 lines of ODS text which seems to be causing the second sheets filters to move down 5 rows.

If I comment out the ODS Text lines the autofilters work fine on the second sheet.

Any suggestions are welcome.

ODS EXCEL FILE="&OUT1."

style=barrettsblue

options(

sheet_interval="none"

sheet_name="Summaries"

);

 ods escapechar='~';

ods text="~S={font_size=14pt font_weight=bold}~UM Auth Turnaround Time";

ods text="~S={font_size=12pt font_weight=bold font_style=italic}~Auths Received Between &BEG_PRINT. and &END_PRINT.";

ods text="~S={font_size=12pt font_weight=bold font_style=italic}~Summary by User, User Type & Aggregate";

ods text="~S={font_size=12pt font_weight=bold}~ ";

ods text="~S={font_size=12pt font_weight=bold font_style=italic}~Aggregate Summary";

proc report data=SUMM;

run;

ods text="~S={font_size=12pt font_weight=bold font_style=italic}~Summary By User Type";

proc report data=SUMMTYPE;

run;

ods text="~S={font_size=12pt font_weight=bold font_style=italic}~Summary By User";

proc report data=SUMMUSER;

run;

ODS EXCEL style=barrettsblue

options(

frozen_headers="1"

autofilter="ALL"

sheet_name="Detail"

);

proc report data=UMSV1;

run;

   ods excel close;

[Cynthia_sas]

Hi:

  I'm not sure what's going on. This works for me:

ods excel file='/folders/myfolders/all_output/test_filters.xlsx'
    options(autofilter='none' sheet_name='No filters');
proc print data=sashelp.class;
run;
 
ods excel options(autofilter='3-5' sheet_name='Filters on 3 4 5');
proc print data=sashelp.cars(obs=20);
run;
 
ods excel options(autofilter='all' sheet_name='filter all');
proc print data=sashelp.shoes(obs=20);
run;
 
ods excel close;

I get no filters in the first sheet, 3 columns with filters in the second sheet and all columns with filters in the 3rd sheet. The key is resetting the value for filters each time. As for the statement that the filters show up in row 6, that sort of makes sense because you have so many lines of ODS TEXT (not sure why you're using that instead of a TITLE statement with embedded_titles, but that's another issue). But as for WHY the ODS TEXT is impacting the filters on the subsequent sheets, that is a question for Tech Support.

Anyway, for help with WHERE the filter is going, with your specific code, you might want to work with Tech Support. If you have discovered a bug, that is something they need to investigate and report.

cynthia