DATA Step, Macro, Functions and more

how to eliminate the effect of system options?

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

how to eliminate the effect of system options?

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:

2016-07-20_212008.png

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?


Accepted Solutions
Solution
‎07-21-2016 02:48 PM
Super User
Posts: 19,814

Re: how to eliminate the effect of system options?

 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. 

View solution in original post


All Replies
Super Contributor
Posts: 440

Re: how to eliminate the effect of system options?

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. 

Contributor
Posts: 20

Re: how to eliminate the effect of system options?

Posted in reply to jklaverstijn
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?
Super User
Posts: 19,814

Re: how to eliminate the effect of system options?

Why do you have an XLS file? 

 

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

Super User
Posts: 10,028

Re: how to eliminate the effect of system options?

[ Edited ]

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;

  

Contributor
Posts: 20

Re: how to eliminate the effect of system options?

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

2016-07-21_154453.png

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

Solution
‎07-21-2016 02:48 PM
Super User
Posts: 19,814

Re: how to eliminate the effect of system options?

 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. 

Contributor
Posts: 20

Re: how to eliminate the effect of system options?

OK, Thank you.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 454 views
  • 0 likes
  • 4 in conversation