topic import excel from , skipping first 7 lines in SAS Programming

import excel from , skipping first 7 lines

fengyuwuzu — Tue, 27 Sep 2016 18:21:51 GMT

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.

Re: import excel from , skipping first 7 lines

SASKiwi — Tue, 27 Sep 2016 19:06:55 GMT

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#n0msy4hy1so0ren1acm90iijxn8j.htm

Re: import excel from , skipping first 7 lines

PGStats — Tue, 27 Sep 2016 19:26:31 GMT

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

Re: import excel from , skipping first 7 lines

user24feb — Wed, 28 Sep 2016 07:22:04 GMT

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