Drop column in ODS

[Q1983]

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 ???

[Cynthia_sas · Accepted solution]

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