topic Re: Problems importing .xls: excelcs does not work with range="A3:0", dbms=xls imports 1st in SAS Procedures

Problems importing .xls: excelcs does not work with range="A3:0", dbms=xls imports 1st column only

js5 — Thu, 09 Aug 2018 13:52:27 GMT

Dear Community,

I am having problems importing an .xls file. I am using 64-bit SAS and 32-bit MS Office which means that I only have two DBMS choices: xls and excelcs. Unfortunately, neither of them does exactly what is required.

The excel file has data starting in cell A3 and up to column AJ, number of rows changing from import to import. With excelcs, I cannot get the following to work:

proc import datafile="foo.xls"
out=excel_test
dbms=excelcs
replace;
range="A3:0";

This throws an error message saying File _IMEX_.'A3:0'n.DATA does not exist. Replacing A3:0 with A3:AJ146 does get the file to import, but as the length of the file is variable, this would mean the code needs to be adapted every time the length changes. According to the documentation [1], RANGE option with 0 for last input row autodetection is supposed to work with EXCELCS.

Unfortunately, switching to xls engine is of no help. The following code:

proc import datafile="foo.xls"
	out=excel_test
	dbms=xls
	replace;
	startrow=4;
	namerow=3;

does produce a dataset with the right number of variables and observations, but except for the first column nothing is actually imported - all the remaining columns are full of missing values.

Is there anything else I could try in order to get a procedure which imports all values and takes care of detecting how many rows are in the input file? Thank you for suggestions in advance.

Best regards,

Julian

[1] http://documentation.sas.com/?docsetId=acpcref&docsetTarget=n0msy4hy1so0ren1acm90iijxn8j.htm&docsetVersion=9.4&locale=en#n0bh60l5spojxzn11t7vvq0d55ka

Re: Problems importing .xls: excelcs does not work with range="A3:0", dbms=xls imports 1st

RW9 — Thu, 09 Aug 2018 14:06:13 GMT

Sorry, this is going to sound very tired to a new poster, but:
Why are you using XLS file format which has been decommissioned well over 10 years now.

Why are you using proc import which is at best a guessing procedure to import data.

If you can't get the data in a decent format, i.e. something open and portable like csv, then do it yourself with save as from Excel. All Excel data types are both proprietary, non-portable, and pretty bad for any type of data transfer.

Once you have a decent datatype, then write a datastep to import the data according to your data import agreement which will define the data structure.

To note, you are having to use the pcfiles engine and excelcs/xls due to using old file formats, switching to modern open file formats will make your life so much easier.

To answer the range part, you need to supply a range, don't believe you can use &xlend, or if that version of excel even supports it. You could just specify :

A3:AJ3000

And then drop rows where col1 is missing.

Re: Problems importing .xls: excelcs does not work with range="A3:0", dbms=xls imports 1st

js5 — Fri, 10 Aug 2018 06:23:52 GMT

Thank you for your honest opinion! I am no fan of .xls format either and I am quite surprised myself why this format is still enjoying such a wide use almost 12 years after a better replacement has been released. The reason why I was trying to get .xls imported directly was that I was trying to load the data in as few steps and using as few pieces of software as possible.

Thank you for the tip with supplying a large range! It turns that at least with the offending file dropping of the empty rows is not even required, no empty observations are being read.