DATA Step, Macro, Functions and more

ODS Autofilter for Second row

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

ODS Autofilter for Second row

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


SAS.png

Accepted Solutions
Solution
‎07-12-2017 12:24 PM
Frequent Contributor
Posts: 89

Re: ODS Autofilter for Second row

Posted in reply to XMonsterX

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:

 

autofilter second row in spreadsheet.jpg

View solution in original post


All Replies
Solution
‎07-12-2017 12:24 PM
Frequent Contributor
Posts: 89

Re: ODS Autofilter for Second row

Posted in reply to XMonsterX

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:

 

autofilter second row in spreadsheet.jpg

Occasional Contributor
Posts: 13

Re: ODS Autofilter for Second row

Posted in reply to SuzanneDorinski

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 216 views
  • 1 like
  • 2 in conversation