How to import excel data in which data starts in row 9

[knveraraju91]

Dear,

I need help in my code in importing excel data. The excel file contains data starting from row 9. The row 9 contains variable names. In row 1-8 there is other information about study. I need to import only data starting from row 9. Please help in my code.

Code;

proc import datafile="C:\Users\ana\raw/data.xls"       DBMS=xls            out=out;

run;

[Shmuel]

Add a  DATAROW option and try:

proc import datafile="C:\Users\ana\raw/data.xls"      

                   DBMS=xls    

                   DATAROW = 9    /* 1st row to output */       

                   out=out;

run;

[Ksharp]

proc import datafile="/folders/myfolders/shoe.xls"      
                   DBMS=xls    replace out=out;
                   STARTROW = 9  ;  /* 1st row to output */       
run;

[Shmuel]

According to SAS Documentatio, the option is DATAROW.

See:  http://www.listendata.com/2013/09/importing-excel-data-into-sas.html

STARTROW has different meaning and is not mentiond with PROC IMPORT.

Maybe new versions accept also STARTROW.

Better check on WINDOWS environment both options.

[Ksharp]

If I was right, datarow= is only for text file like CSV ,TXT ...while startrow= is for XLS engine .
Here is what I got :




 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 51         
 52         
 53         proc import datafile="/folders/myfolders/have.xls"
 54                            DBMS=xls    replace out=out;
 55                            STARTROW = 9  ;  /* 1st row to output */
 56         run;
 
 NOTE: The import data set has 12 observations and 5 variables.
 NOTE: WORK.OUT data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.02 seconds
       cpu time            0.02 seconds
       
 
 57         
 58         
 59         
 60         
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.01 seconds
       cpu time            0.01 seconds
       
 NOTE: The SAS System stopped processing this step because of errors.
 61         proc import datafile="/folders/myfolders/have.xls"
 62                            DBMS=xls
 63                            DATAROW = 9    /* 1st row to output */
                               _______
                               22
                               202
 ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE, 
               _DEBUG_.  
 
 ERROR 202-322: The option or parameter is not recognized and will be ignored.
 
 64                            out=out;
 65         run;
 66         
 67         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 77         

[Ksharp]

Interesting. Both option could work for XLS engine.


 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 51         
 52         
 53         proc import datafile="/folders/myfolders/have.xls"
 54                            DBMS=xls    replace out=out;
 55                            STARTROW = 9  ;  /* 1st row to output */
 56         run;
 
 NOTE: The import data set has 12 observations and 5 variables.
 NOTE: WORK.OUT data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.02 seconds
       cpu time            0.02 seconds
       
 
 57         
 58         
 59         
 60         
 61         proc import datafile="/folders/myfolders/have.xls"
 62                            DBMS=xls    replace out=out;
 63                            DATAROW = 9 ;   /* 1st row to output */
 64         run;
 
 NOTE: The import data set has 12 observations and 5 variables.
 NOTE: WORK.OUT data set was successfully created.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.02 seconds
       cpu time            0.01 seconds
       
 
 65         
 66         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 76