BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RavenWu
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

 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

7 REPLIES 7
jklaverstijn
Rhodochrosite | Level 12

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
Calcite | Level 5
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
Super User

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

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
Calcite | Level 5

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.

Reeza
Super User

 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
Calcite | Level 5
OK, Thank you.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1709 views
  • 0 likes
  • 4 in conversation