topic Re: ODS Autofilter for Second row in SAS Programming

ODS Autofilter for Second row

XMonsterX — Wed, 05 Jul 2017 17:45:29 GMT

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

Re: ODS Autofilter for Second row

SuzanneDorinski — Fri, 07 Jul 2017 02:23:47 GMT

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:

Re: ODS Autofilter for Second row

XMonsterX — Wed, 12 Jul 2017 16:26:52 GMT

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

Re: ODS Autofilter for Second row

KirkLafler — Tue, 01 May 2018 13:25:44 GMT

Nice tip!