I am using the following code to export 3 SAS datasets to separate sheets in an Excel file. The exporting works fine, the file is small and has no bells or whistles. The problem is, when I open the Excel file, all 3 tabs are selected. When all tabs are selected, any change made on any cell will alter that cell on all selected tabs, which could be trouble if a user does not notice that all tabs are selected. One can manually open the Excel file, click on any tab but the first one, and then only that (single) tab/sheet will be selected.
Is there a way to tell SAS (or Excel?) not to select all sheets by default after SAS creates them? I have many of these sheets and would like to avoid opening each one, clicking on one of the tabs, then saving it. (I'm sure it can be done on a folder of files in VBA, but I am hoping SAS has some way to change this grouping behavior).
Thanks.
---------------------------
LIBNAME finbook EXCEL 'C:\test.xlsx' VER=2007;
proc sql;
create table finbook.data as
select * from ds.Final_3_named;
create table finbook.variables as
select * from ds.datainfo;
quit;
DATA finbook.class;
SET sashelp.class;
run;
libname finbook clear;
---------------------------
I don't have that issue and haven't seen it before.
Do you have a run before the final libname statement though? I get errors if I don't.
I also don't specify ver=2007 in the original libname.
I don't have that issue and haven't seen it before.
Do you have a run before the final libname statement though? I get errors if I don't.
I also don't specify ver=2007 in the original libname.
Hi Reeza - I corrected the first post, I think I left that 'run;' part out of what I pasted in.
Anyways I tried by specifying .xls file and commented out the 2007, and it worked in that the tabs were not selected (grouped) when I opened the file. I guess that is a simple work around, even though puzzling why it happens with version = 2007. Thank you for replying.
You can look at using excelcs and pc files server instead of ver=2007 to get an xlsx file if you want.
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.