topic Re: Worksheet names in excel in New SAS User

Worksheet names in excel

librasonali — Tue, 17 Nov 2020 07:20:12 GMT

How to get an excel worksheet name ?

example I have a excel datasheet.xlsx having sheet1 name something so, how to find sheet1 , sheet2... sheetn names?

my prog :

libname cert xlsx "/home/u49388206/cert/excel/datasheet.xlsx";

data out;
set sashelp.vtable;
where libname='cert';
run;

libname cert clear;

PFB log file

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         libname cert xlsx "/home/u49388206/cert/excel/datasheet.xlsx";
 NOTE: Libref CERT was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /home/u49388206/cert/excel/datasheet.xlsx
 74         
 75         data out;
 76         set sashelp.vtable;
 77         where libname='cert';
 78         run;
 
 NOTE: There were 0 observations read from the data set SASHELP.VTABLE.
       WHERE libname='cert';
 NOTE: The data set WORK.OUT has 0 observations and 41 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              5537.87k
       OS Memory           35752.00k
       Timestamp           11/17/2020 07:18:12 AM
       Step Count                        61  Switch Count  2
       Page Faults                       0
       Page Reclaims                     225
       Page Swaps                        0
       Voluntary Context Switches        13
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 79         libname cert clear;
 NOTE: Libref CERT has been deassigned.
 80         
 81         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 93

Re: Worksheet names in excel

Kurt_Bremser — Tue, 17 Nov 2020 06:42:30 GMT

When a program does not work to your satisfaction (and especially when it throws ERRORs, WARNINGs or NOTEs you don't understand), posf the complete log from the offending step. Copy/paste the log text into a window opened with the </> button.

And supply more details. "Did not work" on its own tells us nothing.

Re: Worksheet names in excel

librasonali — Tue, 17 Nov 2020 07:36:38 GMT

thank-you !
i have updated my question

Re: Worksheet names in excel

Kurt_Bremser — Tue, 17 Nov 2020 07:52:23 GMT

I strongly suspect that the pathname to your Excel file is not correct.

LIBNAME XLSX will not throw any messages if the file can't be found, as it can be used to create a new file. The file would then be created upon the first write to this library, and if the path can't be found, you'd get the ERROR then.

Search for the file in your Folders navigation pane, right-click on it, and copy the path from the Properties.

UNIX (the operating system of SAS on Demand) is case sensitive, so you need to get the spelling of directory and file names right. If your file was named Datasheet.xlsx, your LIBNAME won't find it.

PS belay that.

I see that you used 'cert' in your DATA step. Dataset and library names in SASHELP.VTABLE (and all other DICTIONARY tables that contain these columns) are always uppercase, so need to run

data out;
set sashelp.vtable;
where libname='CERT';
run;

Re: Worksheet names in excel

andreas_lds — Tue, 17 Nov 2020 07:54:23 GMT

Afaik the values of libname in sashelp.vtable are almost always in upcase, so try

where libname='CERT';

Re: Worksheet names in excel

Kurt_Bremser — Tue, 17 Nov 2020 08:12:34 GMT

Even with VALIDMEMNAME=EXTEND and a name literal, the letters are converted to uppercase:

data work.'$Class'n;
set sashelp.class;
run;

data test;
set sashelp.vtable;
where libname = "WORK";
run;

Results in $CLASS on my University Edition.

Re: Worksheet names in excel

librasonali — Mon, 23 Nov 2020 09:52:57 GMT

@Kurt_Bremser ,

Do we have alternative logic to find the worksheets name of a xslx sheet ?
( except the above I just mentioned) . IF you know then please share. Thanks in advance !

Re: Worksheet names in excel

Kurt_Bremser — Mon, 23 Nov 2020 10:09:53 GMT

You can query DICTIONARY.TABLES in PROC SQL (SASHELP.VTABLE is a SQL view to this table).

You can run PROC DATASETS and use ODS to redirect the directory listing in the output to a dataset.

All these need a successful LIBNAME to the Excel file to work.

Since XLSX files are zip-compressed archives, you can open them with FILENAME ZIP and then read the contents, but that will be quite tedious compared to the above methods.

Do a Google search for "sas directory listing of zip file" to find some examples.