The SAS Output Delivery System and reporting techniques

Drop column in ODS

Accepted Solution Solved
Reply
Regular Contributor
Posts: 180
Accepted Solution

Drop column in ODS

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

 

 


Accepted Solutions
Solution
‎04-02-2018 01:51 PM
SAS Super FREQ
Posts: 9,424

Re: Drop column in ODS

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

View solution in original post


All Replies
Solution
‎04-02-2018 01:51 PM
SAS Super FREQ
Posts: 9,424

Re: Drop column in ODS

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 149 views
  • 1 like
  • 2 in conversation