Proc Import Starting at row 7 when dbms=ExcelCS

[JSCohen]

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;

[ballardw · Accepted solution]

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.

[Reeza]

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.