The SAS Output Delivery System and reporting techniques

SAS ODS Excel Autofilter

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

SAS ODS Excel Autofilter

Hello Everyone,

 

I am using ODS Excel instead of ODS Tagsets.Excelxp for my current projects. It went all good so far and I like it better than the tagsets.excelxp though it has some differences.

I am creating .xlsx file named 'CRE Reporting for 03312017.xlsx'. It has three sheets-named sheet one, sheet two and sheet three. I have used the option:

 Autofilters='ALL' for all three sheets.

When I first open the produced file, the Filter option under the 'Data' in Excel becomes inactive and I cannot do filtering. It is active in second and third sheets. But, if i come back to the first sheet from sheet two or sheet three then the Filter becomes active for sheet one as well and I can do filtering. If I close the file without saving the changes for sheet one, I again need to go to sheet two or sheet three to make it active for sheet one. 

Is that an issue with the ODS Excel that needs to be fixed or is there a fix for that which I am not aware of? There is no such issue with ODS Tagset.excelxp.

Please help me out.

 

Thank you,

Shankar


Accepted Solutions
Solution
‎04-10-2017 10:56 AM
SAS Super FREQ
Posts: 8,868

Re: SAS ODS Excel Autofilter

Hi:
This appears to be an unintentional side-effect of creating the grouped sheets in the workbook. There is a hot fix for this and it is fixed in SAS 9.4M4. Here's a link to the SAS Note which has the hot fix.
http://support.sas.com/kb/56878

cynthia

View solution in original post


All Replies
Solution
‎04-10-2017 10:56 AM
SAS Super FREQ
Posts: 8,868

Re: SAS ODS Excel Autofilter

Hi:
This appears to be an unintentional side-effect of creating the grouped sheets in the workbook. There is a hot fix for this and it is fixed in SAS 9.4M4. Here's a link to the SAS Note which has the hot fix.
http://support.sas.com/kb/56878

cynthia
Occasional Contributor
Posts: 6

Re: SAS ODS Excel Autofilter

Posted in reply to Cynthia_sas

Thank you Cynthia for your quick reply.

I don't have the admin privilege for that so I am going to contact them for those hot fixes.

 

Shankar 

Valued Guide
Posts: 505

Re: SAS ODS Excel Autofilter

ODS Excel: How to create a working autofilter for all worksheets

My admin gives me temporary 'admin' privileges so I can update my power workstationS SAS.
I can also update and add packages to R, Perl and Python.
You have a seviceabilty issue with SAS, especially on the server. Always use RASPF to access
your software purchases. Reliability, Availability, Serviceability, Performance and Functionality.
IT should provide RASPF benchmarks for server and power workstation SAS.

They don't let me update SAS on the server?
see https://goo.gl/tbBbPJ https://communities.sas.com/t5/ODS-and-Base-Reporting/SAS-ODS-Excel-Autofilter/m-p/348673 for output https://www.dropbox.com/s/85178ipgfmvrh1w/otoftr1.xlsx?dl=0 see posting for hotfix. I am a real fan od 'ods excel' and I hope they fix this bug? My failuers are on the end od this postt. If you have IML/R you can just paste my R code into IML. Seems like a very hard bug (unless you insert a first sheet without autofilter). All of these failed 1. Adding and hiding an additional first sheet after (R XLConnect) 2. Adding and removing the additional first sheet (R XLConnect) 3. Turning off autofilter off and adding it later with (R XLConnect) 4. Just dropping the additional first sheet. I did not try python, too lazy
SOLUTION
The only solution that worked for me was to create the three sheets entirely in R. Note you may be able create a highly stylized templale with three sheets without autofilter using 'ods excel', clear the sheets ie SQL drop table, and load the data with autofilter using R. SOAPBOX ON It would be really nice if SAS would make all submitted 'bug' reports available, like most open source products. Even if not a bug it could save programmers hours of watsed time. SOAPBOX OFF HAVE === Up to 40 obs from sashelp.class total obs=19 Obs NAME SEX AGE HEIGHT WEIGHT 1 Alfred M 14 69.0 112.5 2 Alice F 13 56.5 84.0 3 Barbara F 13 65.3 98.0 .... 18 Thomas M 11 57.5 85.0 19 William M 15 66.5 112.0 WANT (a working autofilter on all three sheets - not just sheets MALES and FEMALES =================================================================================== SHEET ALL IN WORKBOOK D:/XLS/HAVE.XLSX +----------------------------------------------------------------+ | A [V] | B [V] | C [V] | D [V] | E [V] | +----------------------------------------------------------------+ 1 | NAME | SEX | AGE | HEIGHT | WEIGHT | +------------+------------+------------+------------+------------+ 2 | ALFRED | M | 14 | 69 | 112.5 | +------------+------------+------------+------------+------------+ ... +------------+------------+------------+------------+------------+ N | WILMA | F | 15 | 66.5 | 112 | +------------+------------+------------+------------+------------+ [ALL] SHEET MALES IN WORKBOOK D:/XLS/HAVE.XLSX +----------------------------------------------------------------+ | A [V] | B [V] | C [V] | D [V] | E [V] | +----------------------------------------------------------------+ 1 | NAME | SEX | AGE | HEIGHT | WEIGHT | +------------+------------+------------+------------+------------+ 2 | ALFRED | M | 14 | 69 | 112.5 | +------------+------------+------------+------------+------------+ ... +------------+------------+------------+------------+------------+ N | WILLIAM | M | 15 | 66.5 | 112 | +------------+------------+------------+------------+------------+ [MALES] SHEET FEMALES IN WORKBOOK D:/XLS/HAVE.XLSX +----------------------------------------------------------------+ | A [V] | B [V] | C [V] | D [V] | E [V] | +----------------------------------------------------------------+ 1 | NAME | SEX | AGE | HEIGHT | WEIGHT | +------------+------------+------------+------------+------------+ 2 | ALICE | F | 14 | 69 | 112.5 | +------------+------------+------------+------------+------------+ ... +------------+------------+------------+------------+------------+ N | WILMA | F | 15 | 66.5 | 112 | +------------+------------+------------+------------+------------+ [FEMALES] WORKING CODE ============ R setAutoFilter(wb, sheet = "females", reference = aref("A1", dim(females))); * _ _ _ _ __ ___ __ _| | _____ __| | __ _| |_ __ _ | '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` | | | | | | | (_| | < __/_____| (_| | (_| | || (_| | |_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_| ; options validvarname=upcase; libname sd1 "d:/sd1"; data sd1.class; set sashelp.class; run;quit; * _ _ _ ___ ___ | |_ _| |_(_) ___ _ __ / __|/ _ \| | | | | __| |/ _ \| '_ \ \__ \ (_) | | |_| | |_| | (_) | | | | |___/\___/|_|\__,_|\__|_|\___/|_| |_| ; %utlfkil(d:/xls/otoftr1.xlsx); %utl_submit_r64(' source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T); library(XLConnect); library(haven); have<-read_sas("d:/sd1/class.sas7bdat"); females <- have[which(have$SEX=="F"),]; males <- have[which(have$SEX=="F"),]; wb <- loadWorkbook("d:/xls/otoftr1.xlsx", create = TRUE); createSheet ( wb , "all" ); writeWorksheet(wb,have,sheet="all"); setAutoFilter(wb, sheet = "all", reference = aref("A1", dim(have))); createSheet ( wb , "females" ); writeWorksheet(wb,females,sheet="females"); setAutoFilter(wb, sheet = "females", reference = aref("A1", dim(females))); createSheet ( wb , "males" ); writeWorksheet(wb,have,sheet="males"); setAutoFilter(wb, sheet = "males", reference = aref("A1", dim(males))); saveWorkbook(wb,"d:/xls/otoftr1.xlsx"); '); * __ _ _ / _| __ _(_) |_ _ _ __ ___ ___ | |_ / _` | | | | | | '__/ _ \/ __| | _| (_| | | | |_| | | | __/\__ \ |_| \__,_|_|_|\__,_|_| \___||___/ ; * this did not work title; footnote; %utlfkil(d:\xls\ctoftr.xlsx) ; ods excel file="d:\xls\otoftr.xlsx"; ods excel options(sheet_name="all" autofilter= yes'); proc print data=sashelp.class;run;quit; ods excel options(sheet_name="males" autofilter= 'yes'); proc print data=sashelp.class(where=(sex='M'));run;quit; ods excel options(sheet_name="females" autofilter= 'yes'); proc print data=sashelp.class(where=(sex='F'));run;quit; ods excel close; * this just clered the cell; libname xel "d:\xls\otoftr.xlsx"; proc sql; drop table xel.'xxx$'n ; quit; libname xel clear; * remove extra sheeet did not work (sheet all still had the problem; * this did remove the sheet; %utl_submit_r64(' source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T); library(XLConnect); wb <- loadWorkbook("d:/xls/otoftr.xlsx"); removeSheet(wb, sheet = "xxx"); saveWorkbook(wb,"d:/xls/otoftr.xlsx"); '); * remove extra sheeet did not work (sheet all still had the problem; %utl_submit_r64(' source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T); library(XLConnect); wb <- loadWorkbook("d:/xls/otoftr.xlsx"); hideSheet(wb, "xxx",); saveWorkbook(wb,"d:/xls/otoftr.xlsx"); '); * remove extra sheeet did not work (sheet all still had the problem; %utl_submit_r64(' source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T); library(XLConnect); library(haven); wb <- loadWorkbook("d:/xls/otoftr.xlsx"); setAutoFilter(wb, sheet = "females",reference="A1:F1"); setAutoFilter(wb, sheet = "males",reference="A1:F1"); setAutoFilter(wb, sheet = "all",reference="A1:F1"); saveWorkbook(wb,"d:/xls/otoftr.xlsx"); '); * _ _ _ _ _ __ _ _ _ _| |_| | ___ _ _| |__ _ __ ___ (_) |_ _ __ / /_ | || | | | | | __| |_____/ __| | | | '_ \| '_ ` _ \| | __|____| '__| '_ \| || |_ | |_| | |_| |_____\__ \ |_| | |_) | | | | | | | ||_____| | | (_) |__ _| \__,_|\__|_| |___/\__,_|_.__/|_| |_| |_|_|\__| |_| \___/ |_| ; %macro utl_submit_R64(pgmx)/des="Semi colon separated set of R commands"; * write the program to a temporary file; filename r_pgm temp lrecl=32766 recfm=v; data _null_; file r_pgm; pgm=&pgmx; put pgm; putlog pgm; run; %let __loc=%sysfunc(pathname(r_pgm)); * pipe file through R; filename rut pipe "c:\Progra~1\R\R-3.3.2\bin\x64\R.exe --vanilla --quiet --no-save < &__loc"; data _null_; file print; infile rut; input; put _infile_; putlog _infile_; run; filename rut clear; filename r_pgm clear; %mend utl_submit_r64;
SAS Super FREQ
Posts: 304

Re: SAS ODS Excel Autofilter

One workaround is to right-click on the active sheet, choose Ungroup Sheets from the pop-up menu, and then save the file.

 

Vince DelGobbo

SAS R&D

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 473 views
  • 0 likes
  • 4 in conversation