Select a particular sheet from excel workbook while importing data using data step

[taran]

Below is my code and I want to use sheet pqr from ABC.xls to import. I am using UNIX SAS

DATA SE;

     LENGTH  variable_name type $20 variable_label $100 length $3;

     INFILE "&fileloc./ABC.xls" LRECL=55 firstobs=14 TERMSTR=CRLF  MISSOVER  DSD ;

    INPUT  variable_name    : $CHAR20. variable_label   : $CHAR100. type : $CHAR20. length  : $CHAR3. ;

RUN;rt u

[RichardinOz]

taran

Unless you have renamed a text file with an .xls extension you cannot read Excel files with an infile statement in a SAS data step.

Options available to you:

Create a CSV or TXT file from within Excel and use Proc Import to import the data, or write a datastep with the appropriate delimiters

License SAS Access to PC Files on your server and use Proc Import

You may also be able to export your spreadsheet as an  .xml file and import that into SAS

Richard

[RW9]

Just to add there are other options also.  The first being a libname:

http://www2.sas.com/proceedings/sugi31/024-31.pdf

And the other being DDE (which I don't recommend).

[RW9]

Also, if its xlsx you could read the file directly ->

1) Unzip ...xlsx to folder y

2) read y\xl\worksheets\sheetx.xml

[Peter_L]

Here is an example of some typical code I use for reading a sheet from Excel:

libname ex EXCEL 'myfile.xlsx';

data tr_ex;

set ex.'Mysheetname$'n;

...

run;

Note the $ symbol at the end of the sheet name and the n after the quoted string to identify it as a name.

The advantage of the libname approach is that you can find the names of all sheets (and ranges) in the workbook using the sashelp.vtable dataset view.

Note that the names of columns are mangled by the Microsoft drivers to replace some special characters in the resulting variable names. The variable labels contain the original column headings from Excel in most cases but not all; some mangling occurs here too.

I advise strongly against using DDE unless you have to. To read data it is not necessary.

Peter Lancashire

[Kurt_Bremser · Accepted solution]

Most of the options the afore posters have given (libname excel, proc import) miss an important fact: they are only available on Windows, but not on UNIX.

Export the data from excel as .csv, transfer the file to UNIX, and read it there. That way you have the additional advantage of controlling the interface (the .csv) with a simple text editor.

[Peter_L]

Yes, you are right. SAS has no simple way that I know of to read the specific CSV format Excel writes. Beware different date formats and decimal separators when writing and reading CSV format. The most reliable solution is to write your own CSV exporter macro for Excel with ISO date format, points for decimals and whatever consistent string quoting or escaping convention you prefer. In my view a tab character is a better separator than a comma.

[Kurt_Bremser]

The default separator (at least in the German locale) is the semicolon when excel writes to a csv file.

One has to make sure that the columns in the spreadsheet have a format assigned that corresponds to the informat one will use in the data step. Then the data will arrive healthy in the SAS table.

[Peter_L]

Thanks for the information about the German locale. The files should be called SSV .

I sit in Germany but have an English locale. As I wrote: it is not consistent. For international work you must write your own macro for Excel or write one SAS method for each locale of CSV file origin. Date will give you a problem too because the USA has MM/DD/YYYY and Europe has DD/MM/YYYY. Only YYYY/MM/DD or YYYYMMDD is unambiguous. I work in an international company and we are still occasionally plagued by these problems when data travels via a text format such as CSV.

Peter Lancashire, Germany

[RichardinOz]

Peter

Hmm, I did not know semicolons could be used in a "CSV".  Does Proc Import recognise semicolons if the SAS locale is correctly set?

I regard use of a CSV as a counsel of despair because there is no universal standard, and CSV files contain no metadata apart from (usually) the column names, hence problems reading values which could be treated as numeric or character data.  Tab separated values avoid some of the problems with embedded commas, though since Excel cells can contain newline codes these also cause problems.

I should have mentioned Enterprise Guide has its own method for importing and exporting to Excel.

The option to specify libname using an EXCEL engine I believe requires the SAS Access product, which is available for UNIX intallations

Richard

[Kurt_Bremser]

If the Access to PC Files product is licensed on UNIX, LIBNAME XLS or LIBNAME XLSX can be used.

LIBNAME EXCEL only works on windows.

[taran]

Thank You all for your response, I wont be able to get ACCESS installed on my system, so cant use proc import, looks like the best option for me is to convert these files in to csv and use libname. I have more than 400 such files and would need to figure out a way to automate that conversion.

Thank you again

[RW9]

A quick google shows plenty of batch xls to csv converters, example:

http://www.batchwork.com/en/xls2csv/

[taran]

Thank You RW9.

There are 2 issues here...i cannot install anything on my system as i dont have admin rights and secondly I want to use SAS to do it as these xls files are delivered from time to time and if SAS program can read it it will be easier for me.

[RW9]

By scripting:

windows - Convert XLS to CSV on command line - Stack Overflow