ODS Autofilter for Second row

[XMonsterX]

Hi,

I have a multi-sheet xlsx that I create using ODS Tagset. I have no issues with creating, or the autofilter, my issue is that it Autofilters the first column, however I want the autofilter to be on the second row (As I have dual headers and don't want to loose the second header while sorting. 

I haven't been able to find a thread or example that would give me the results I need. 

Any help would be very much appreciated. (I've included a screenshot if there's any issue with clarity)

Thanks,

Monster

[SuzanneDorinski · Accepted solution]

If you use PROC REPORT, you can specify the headers in the first row on the COLUMNS statement, and then use the DEFINE statements to specify the headers for the second row.  When I do that, the autofilters show up on the second row.

Example code:

ODS EXCEL FILE='/folders/myfolders/SAS Communities/autofilter_bottom_of_headers.xlsx'
  options(autofilter='on'
          sheet_name='Autofilter 2nd row'
          absolute_column_width='20,16,16,16,16');

proc report data=sashelp.class;
  columns ('Name' name) 
          ('Sex' sex) 
          ('Age' age)
          ('Height' height)
          ('Weight' weight);
  define name / 'First name only';
  define sex / '';
  define age / '';
  define height / '(inches)';
  define weight / '(lbs)';
run;

ODS EXCEL CLOSE;

Example output:

[XMonsterX]

Sorry for the late reply, things have been too busy at work to get time to test...

This worked perfectly. My headers are much more complex than these so it took a little playing around, but the concept was dead on.

Thanks so much for the quick response Suzanne.

Monster

[KirkLafler]

Nice tip!