topic Re: how to eliminate the effect of system options? in SAS Programming

how to eliminate the effect of system options?

RavenWu — Wed, 20 Jul 2016 19:53:48 GMT

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?

Re: how to eliminate the effect of system options?

jklaverstijn — Wed, 20 Jul 2016 20:02:44 GMT

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.

Re: how to eliminate the effect of system options?

RavenWu — Wed, 20 Jul 2016 20:32:00 GMT

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?

Re: how to eliminate the effect of system options?

Reeza — Wed, 20 Jul 2016 21:13:28 GMT

Why do you have an XLS file?

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

Re: how to eliminate the effect of system options?

Ksharp — Thu, 21 Jul 2016 01:55:48 GMT

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;

Re: how to eliminate the effect of system options?

RavenWu — Thu, 21 Jul 2016 13:48:56 GMT

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.

Re: how to eliminate the effect of system options?

Reeza — Thu, 21 Jul 2016 13:52:44 GMT

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.

Re: how to eliminate the effect of system options?

RavenWu — Thu, 21 Jul 2016 18:48:47 GMT

OK， Thank you.