How to rename a table in the libraries?

[Mluna]

Hello,

I just started learning SAS. I made a library using the code:

%let path2=~/MOP/myData/Testing.xlsx;
options validvarname=v7 ;
libname SAMPLIB XLSX "&path2";

But the excel file sheet is named Sheet1. I know I can go rename the Sheet name in excel and re-run it.

But that created a couple of questions: can just rename the Sheets by adding an option in the code? and can i rename multiple sheets?

Thank you to anyone that could point me in the right direction. I tried looking it through the documentation but I'm having a hard time finding an answer.

[PaigeMiller]

I'm not 100% sure I understand what you are asking, but ...

how about this?

%let path2=Testing.xlsx;
options validvarname=v7 ;
libname SAMPLIB XLSX "&path2";

data samplib.Class;
    set sashelp.class;
run;
libname samplib;

[Mluna]

So when I Run the code I posted it created that library and the xlsx file has only one sheet, named Sheet1, I want to know if you can change the name of that sheet from the code, so it doesn't just use the default name of the sheet from the xlsx file. 

[PaigeMiller]

You want a blank tab named something other than SHEET1?

[Mluna]

No i'd like to change the name from Sheet1 to something else. 

[Quentin · Accepted solution]

The job of the library statement is to create a libref (in your case SAMPLIB) which points to a logical location that holds tables.  That location could be a SQL server database, or a folder, or many other things.  In your case, the location is an Excel file, that likely has one or more sheets.

The screen shot is showing the names of all of the sheets that exist in the Excel file.  If you want to change the names of those sheets, you would need to update the Excel file.  (I can't remember if you can do this from SAS, but regardless, you would be changing the Excel file.)

Typically I don't worry about the names of the sheets in the Excel file (which is often Sheet1, because 95% of Excel users don't even know that an Excel file can have multiple sheets in it), but I will give the data a better name when I read the data into a SAS dataset. 

So my step to read the data into SAS might look like:

%let path2=~/MOP/myData/Testing.xlsx;
options validvarname=v7 ;
libname SAMPLIB XLSX "&path2";

data Testing;
  set samplib.Sheet1;
run;

That will create a SAS dataset named TESTING, in the WORK library, which has the data from Sheet1 in Testing.xls.

[Mluna]

Thank you. What you said makes sense. Instead of working direct from the library table you can just create a table and name it w/e you want. I wasn't sure what the connection was when creating the libref. But this made sense.

[ballardw]

Libraries that point to most external data sources, such as Excel, are not going to have the "rename" ability  that SAS does use for it's files because you never know what else may be looking for that explicit name and SAS renaming the "table" would break other behaviors. 

For example you can reference a cell on a different sheet inside an Excel workbook by using "Some sheet name"!A4 to reference the cell A4 on the sheet with name "Some sheet name". If you were allowed to rename that sheet then the cell reference would break and other parts of the spreadsheet would be unusable.