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?
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.
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.
Why do you have an XLS file?
Look at the NAMEROW and DATAROW options. For importing data you shouldn't be using system options.
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.