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
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
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.