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
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:
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:
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
Nice tip!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.