BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

1 REPLY 1
Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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