data have;
input ln_no $ state $ loans amt;
datalines;
1123 AL 1 100
1123 AL 1 100
1123 AL 2 150
1233 AL 2 150
CA 3 50
;
run;
ods listing close;
options missing=' ' topmargin=0.30in bottommargin=0.01in rightmargin=0.25in leftmargin=0.25in;
ODS TAGSETS.ExcelXP file="&ReportOut..xls" Path="&OutDir" style=&dors_style;
%macro Final(final,sheet_nm);
ODS TAGSETS.ExcelXP
options(sheet_interval='none'
absolute_column_width='8'
sheet_name=&sheet_nm.
center_horizontal="no"
frozen_headers = "Yes"
Orientation='Landscape'
embedded_titles='No'
fittopage="No"
blackandwhite="No"
Embedded_Footnotes='Yes'
autofit_height="Yes");
%NoAccountLogic(&final.,&ReportName);
PROC REPORT DATA=&final. headskip split='*' wrap nowd
style(report)=[background=black cellspacing=9 just=center font_size=10pt font_face="Calibri" bordercolor=black borderwidth=1]
style(column)=[background=white font_size=9pt bordercolor=black borderwidth=1]
;
COLUMNS _all_;
run;
%mend final;
%Final(have,"population");
ODS TAGSETS.ExcelXP close;
ods listing;
quit;
The data displays without issue. I want to be able to add sortable dropdown columns. The user should be able to view drilldown on each column using a dropdown aarow. If it cannot be down in ODS is there a way to accomplish this using a straight proc export statement ???
Hi:
Are you describing an AUTOFILTER in Excel? Both TAGSETS.EXCELXP and ODS EXCEL support the AUTOFILTER suboption. It looks like you know how to use the suboptions, Have you tried AUTOFILTER="ON"? You can look in the TAGSETS.EXCELXP documentation by doing doc="Help" as a suboption to see the ways to use AUTOFILTER. This worked for me using TAGSETS.EXCELXP:
ods tagsets.excelxp file='c:\temp\af.xml' style=htmlblue
options(autofilter="on" doc="help");
proc report data=sashelp.class;
column age sex name height weight;
run;
ods tagsets.excelxp close;
Otherwise, I'm not sure what you mean when you say that you want "to be able to add sortable dropdown columns. The user should be able to view drilldown on each column using a dropdown aarow."
Cynthia
Hi:
Are you describing an AUTOFILTER in Excel? Both TAGSETS.EXCELXP and ODS EXCEL support the AUTOFILTER suboption. It looks like you know how to use the suboptions, Have you tried AUTOFILTER="ON"? You can look in the TAGSETS.EXCELXP documentation by doing doc="Help" as a suboption to see the ways to use AUTOFILTER. This worked for me using TAGSETS.EXCELXP:
ods tagsets.excelxp file='c:\temp\af.xml' style=htmlblue
options(autofilter="on" doc="help");
proc report data=sashelp.class;
column age sex name height weight;
run;
ods tagsets.excelxp close;
Otherwise, I'm not sure what you mean when you say that you want "to be able to add sortable dropdown columns. The user should be able to view drilldown on each column using a dropdown aarow."
Cynthia
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.