how to eliminate the effect of system options?

[RavenWu]

My first quesion:

After I imported the datafile, I wrote

PROC IMPORT DATAFILE= 'D:\sasprogramming\EX1.xls' OUT= EX1
DBMS=xls REPLACE ;
GETNAMES= yes;
RUN;

options FIRSTOBS =2;

then the output data will read the data from the third row, but even if I delete this statement (OPTION FIRSTOBS=2), and re-run the import statement, the SAS will still read the datafile from the third row, then how could I eliminate the effet of the option statement?

The second question:

 My data looks like:

the variables are in the second row, If I use the following codes:

PROC IMPORT DATAFILE= 'D:\sasprogramming\EX1.xls' OUT= EX1
DBMS=xls REPLACE ;
GETNAMES= yes;
RUN;

the headline of the output will be A B C D..., Apprently SAS threat the A B C D as the variables. then if I add

options FIRSTOBS =2;

 SAS will read the data from the third line, then the variables id, dates....will disappear, so if I want to read the second row as my varibles, what I can do?

[jklaverstijn]

In your case you can revert the FIRSTOBS option to its default by

OPTIONS FIRSTOBS=1;

Deleting an OPTIONS statement does not undo its effects unless you restart SAS. Usually this option is used in a more limited scope that in your case so there is less need to worry about the remainder of the code.

Hope this helps,

- Jan. 

[RavenWu]

Thank you for your response, but if I set FIRSTOBS=1, the variables are indeed display in the output, but SAS do not treat them as the variables, instead, SAS still treat variables (id, dates...) as the data contents, and treat the excel column A B C D as the variables, but actually I want to use the variables id, dates..., so how can I do?

[Reeza]

Why do you have an XLS file? 

Look at the NAMEROW and DATAROW options. For importing data you shouldn't be using system options. 

[Ksharp]

It is RANGE problem. Or you could try other option as Reeza pointed : like  DATAROW options, STARTROW options.....

PROC IMPORT DATAFILE= 'D:\sasprogramming\EX1.xls' OUT= EX1
DBMS=xls REPLACE ;
RANGE='Sheet1$A1:E100';
GETNAMES= yes;
RUN;

[RavenWu]

Thank you for your answer, but it doesn't work. the  SAS still treat A B C D as the variables.

I have attached my excel file. Could you try it and find the solution for this? Thank you.

[Reeza · Accepted solution]

 Do you need to do this multiple times? If not, fix the file manually and move on. You can also assign a named range in Excel and use that. 

Otherwise, unfortunately with an XLS file your options are very limited. 

If XLSX NAMEROW=2; DATAROW=3; will import the file correctly. 

[RavenWu]

OK， Thank you.