BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
XMonsterX
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
SuzanneDorinski
Lapis Lazuli | Level 10

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

3 REPLIES 3
SuzanneDorinski
Lapis Lazuli | Level 10

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

XMonsterX
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4176 views
  • 4 likes
  • 3 in conversation