DATA Step, Macro, Functions and more

import excel from , skipping first 7 lines

Reply
Super Contributor
Posts: 312

import excel from , skipping first 7 lines

My start row starting row 9 and names are in row 8. using the code below I got :

1794 namerow=8;
-------
180
1795 datarow=9;
-------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

 

proc import out= work.spadfw
            datafile= "D:\Projects\Myfile.xlsx" 
            dbms=excel replace;
      *range="sheet1$"; 
      getnames=yes;
      namerow=8;
      datarow=9;
      *mixed=no;
      *scantext=yes;
      *usedate=yes;
      *scantime=yes;
RUN;

Any hints? Thanks in advance. 

Super User
Posts: 3,102

Re: import excel from , skipping first 7 lines

I'm pretty sure those options are only valid for XLS-type files. For XLSX use the RANGE option and GETNAMES to trigger reading column names from the row just above the range:

 

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

 

Respected Advisor
Posts: 4,644

Re: import excel from , skipping first 7 lines

You could also try STARTROW=  instead of DATAROW. It might still work.

PG
Super Contributor
Posts: 336

Re: import excel from , skipping first 7 lines

DBMS=Excel + Datarow does not work for me either. DBMS=XLSX + Datarow should work.

Ask a Question
Discussion stats
  • 3 replies
  • 314 views
  • 0 likes
  • 4 in conversation