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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

View solution in original post

3 REPLIES 3
Reeza
Super User

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.

ucdcrush
Obsidian | Level 7

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.


Reeza
Super User

You can look at using excelcs and pc files server instead of ver=2007 to get an xlsx file if you want.

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!

Discussion stats
  • 3 replies
  • 1139 views
  • 0 likes
  • 2 in conversation