BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
st_012
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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

4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
st_012
Calcite | Level 5

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 

rogerjdeangelis
Barite | Level 11
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;
Vince_SAS
Rhodochrosite | Level 12

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

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
  • 4 replies
  • 5395 views
  • 0 likes
  • 4 in conversation