Drop column in ODS

Q1983 — Mon, 02 Apr 2018 15:29:59 GMT

data have;

input ln_no $ state $ loans amt;

datalines;

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

Re: Drop column in ODS

Cynthia_sas — Mon, 02 Apr 2018 17:49:33 GMT

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

topic Drop column in ODS in ODS and Base Reporting

Drop column in ODS

Re: Drop column in ODS