topic Re: Proc Import Starting at row 7 when dbms=ExcelCS in SAS Programming

Proc Import Starting at row 7 when dbms=ExcelCS

JSCohen — Tue, 06 Jun 2017 15:30:05 GMT

Hello,

I am trying to import Excel spreadsheets across platforms. I am using the following code but I get an error that Datarow is not valid. My data starts in row 7 and the column headings are in row6.

proc import FILE="\\...My file.xlsx"

out=Myfileout

dbms=excelcs /*ExcelCS is required else I get "Class not registered" */

replace;

sheet="Dashboard";

datarow=7;

run;

Re: Proc Import Starting at row 7 when dbms=ExcelCS

ballardw — Tue, 06 Jun 2017 15:59:45 GMT

As the error says, datarows is not supported for Excel. You could try specifying a range to import such as Range="sheetname$a6:z200" but you're not going to get variable names from row 6 and would have to use Proc datasets or similar to assign names you want.

Or save the file as CSV and remove the first 5 rows before importing.

Proc import expects "clean" data, column headings if any in the first row and then data. If the data is clean, a common issue with Excel, then you have to do more work. Or avoid using Excel files for data interchange if possible.

Re: Proc Import Starting at row 7 when dbms=ExcelCS

Reeza — Tue, 06 Jun 2017 17:39:36 GMT

Depending on your SAS version, DBMS=XLSX may also work (SAS 9.3 TS2M3+).

As specified you can use a RANGE.

I thought it would still include the names when you specified the RANGE but you can probably test that.