topic Re: How to import excel data in which data starts in row 9 in SAS Procedures

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

knveraraju91 — Mon, 29 Aug 2016 15:18:38 GMT

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;

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

Shmuel — Mon, 29 Aug 2016 15:50:31 GMT

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;

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

Ksharp — Tue, 30 Aug 2016 03:40:52 GMT


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

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

Shmuel — Tue, 30 Aug 2016 08:53:54 GMT

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.

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

Ksharp — Tue, 30 Aug 2016 13:14:08 GMT

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

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

Ksharp — Tue, 30 Aug 2016 13:20:22 GMT

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