BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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;

5 REPLIES 5
Shmuel
Garnet | Level 18

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
Super User

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

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
Super User
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
Super User
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         


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 12681 views
  • 3 likes
  • 3 in conversation