importing from Excel

[K3K]

I am importing an Excel file into 9.4 using the wizard but need the data to start at line 3 (total 377 lines) and to keep variables in line 1.  I cannot seem to get it to do no matter what.  Help?

[Reeza]

Yeah, I don't think SAS currently supports data in that style. You can import the data set and then drop the first two records easily enough though after.

[K3K]

well how do you drop row 2 because nothing I have done works.

[Tom]

Are you saying that your Excel file looks like:

Name1 Name2
junk row
val1 val2
val3 val4
val5 val6

To drop that extra row just add a data step.

data want ;
  set have (firstobs=2);
run;

Of course the extra row of junk might mess up the SAS's ability to properly define the variable types.

[K3K]

If only, but it is not working. The program below is what I have to work with and datarow=3, firstobs=3, 3n, specifying a range and using getnames=yes - nothing worked.

PROC IMPORT OUT= work.test1a
DATAFILE= "C:\Users\knorwoo1\Documents\My SAS Files\9.4\BIOE
813\test1\Test1A.xls"
DBMS=XLS REPLACE;
GETNAMES=YES;
RANGE="Sheet1$";
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
proc print;

run;

[Reeza]

1. Import file wiht proc import

2. remove first two records. _n_ is a pseudorecord number.

proc import..blah;

data want;
set imported;

if _n_< 3 then delete;

run;

[K3K]

But that deletes the variable names as well. If 1 <_n_ < 3 then delete; you think it will work?



DATAROWS=3; should work but I don't know where it goes in the program as it rejected them all.

[Tom]

What version of SAS are you using. What type of Excel file do you have?  Is it XLSX or some older format?

How is you Excel sheet actually arranged?  Here is a normal sheet.

Name1	Name2
1	2
3	4

Here is one with extra row in the table.

Name1	Name2
1	2
3	4

Here is one with extra row before the table.

Name1	Name2
1	2
3	4

I made an XLSX file with those three sheets and copied the data to work. 

libname in xlsx 'c:\downloads\test3.xlsx';
proc copy inlib=in outlib=work;
run;

Here is what they look like.

Does your file look like any of these?  If not what does it look like?

[K3K]

SAS 9.4 and xls



Variable names are in row 1

Gobble goop I want to skip in row 2

Data begins row 3

[Tom]

If the names are not in the first row then you need to import without names and add the names back later.

[K3K]

They are in the first row but need to skip row two and start data at row 3

[Tom]

If the names are in the first row then the only option you need is the datarow option.

proc import datafile='c:\downloads\test3.xlsx' out=values replace dbms=xlsx ;
  sheet='Sheet4';
  datarow=3;
run;

What XLSX sheet looks like

Name1	Name2
Junk	Junk
1	2
3	4

What SAS dataset looks like

 NOTE: works the same using XLS file instead of XLSX file.

[K3K]

That is what I thought but no. Error says bad command or used out of order

[Tom]

Show your log use the {i} in editor menu bar to open pop-up window to paste the log contents.

Also exactly what version of SAS are you using? 

Are there other issues with the XLS file that might cause trouble for SAS?  Merged cells for example?

And if you can pare down the problem XLS file to a small example that still exhibits the issue then post that as an attachment.  Although most user of forum would be reluctant to download an XLS file from web.

[ballardw]

FWIW I routinely save any Excel file to CSV and work with that as there are more options in Proc Import, such as GUESSINGROWS and data rows. And even then usually modify the data step code generated to read the CSV to be "nicer" in providing useable variable names, consistent lengths if I expect to deal with multiple files in a similar layout, adding variable labels and even data checking code.

Relying on proc import to deal with multiple files often leads to headaches if the data has to be combined later.