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

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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