Isnt there a way to SELECT a xls file simply?

[cellurl1]

I thought I saw something like this. Is something like this possible??

libname dir './dir';

proc sql;

select * from dir.myfile.xls;

Is there a simple simple two liner like that to simply use xls?

[ballardw]

Is it XLS or XLSX?

You cant try a LIBNAME statement pointing to the file without an engine specification and if you have SAS/Access to PCFiles may work.

I VERY strongly recommend examining the data sets created with this libname before attempting to use in any fashion.

Issues arise with 1) sheet names that do not follow standard SAS data set name rules: requires setting Options validmemname=extend;

2) column headings that do not follow standard SAS variable name rules: requires setting: Options validvarname=Any

3) if there are multiple header rows then then the "column" headings may not contain any proper variable indicator and can result in all variables being character.

After you have the libname data set names and variables worked out then any of the SAS procedures should be able to use the data with the above caveats.

If you have to set the validmemname then sheet names would require using 'excel sheet name'n name literal constructs. Similar for columns to name the variables.

[pink_poodle · Accepted solution]

Here is a nice article about that:

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

[Tom]

What do you mean by "select"?

Is the file actually using the ancient XLS file format?  Or is a the modern open standard XLSX file format?

What is the version of SAS you using?

What is the operating system where SAS is running?  

If the file is using XLS then you could try using PROC IMPORT.  Do you know the names of the sheets in the file?  If you don't tell PROC IMPORT which sheet to use it will just use the first one.

proc import file='./dir/myfile.xlsx' out=from_xls replace dbms=xls ;
run;

Now that you have the data in a dataset you can use it for anything you want.  For example if you just want to display it there is no need to use PROC SQL.  Just use PROC PRINT.

proc print data=from_xls;
run;

If the file is using XLSX format you can make a libref that points at the file and then access each of the sheets as if they where datasets.

libname myfile xlsx './dir/myfile.xlsx';

If you don't know the names of the sheets in the file you could try just copying all of them into the WORK library and then you will see the names in the SAS log.

proc copy inlib=myfile outlib=work;
run;