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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

15 REPLIES 15
RichardinOz
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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

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
Quartz | Level 8

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

taran
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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

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

taran
Calcite | Level 5

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 7991 views
  • 6 likes
  • 5 in conversation