DATA Step, Macro, Functions and more

Proc Import Starting at row 7 when dbms=ExcelCS

Accepted Solution Solved
Reply
Learner
Posts: 1
Accepted Solution

Proc Import Starting at row 7 when dbms=ExcelCS

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;


Accepted Solutions
Solution
‎06-06-2017 02:53 PM
Super User
Posts: 11,343

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

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.

View solution in original post


All Replies
Solution
‎06-06-2017 02:53 PM
Super User
Posts: 11,343

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

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.

Super User
Posts: 19,822

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 183 views
  • 0 likes
  • 3 in conversation