topic Re: importing from Excel in SAS Programming

importing from Excel

K3K — Wed, 19 Sep 2018 20:34:47 GMT

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?

Re: importing from Excel

Reeza — Wed, 19 Sep 2018 21:19:33 GMT

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.

Re: importing from Excel

K3K — Wed, 19 Sep 2018 21:21:18 GMT

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

Re: importing from Excel

Tom — Wed, 19 Sep 2018 21:51:31 GMT

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.

Re: importing from Excel

K3K — Wed, 19 Sep 2018 21:55:31 GMT

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;

Re: importing from Excel

Reeza — Wed, 19 Sep 2018 22:06:43 GMT

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;

Re: importing from Excel

Tom — Wed, 19 Sep 2018 22:09:22 GMT

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?

Re: importing from Excel

Tom — Wed, 19 Sep 2018 22:11:20 GMT

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

Re: importing from Excel

K3K — Wed, 19 Sep 2018 22:25:31 GMT

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

Re: importing from Excel

K3K — Wed, 19 Sep 2018 22:26:31 GMT

SAS 9.4 and xls

Variable names are in row 1

Gobble goop I want to skip in row 2

Data begins row 3

Re: importing from Excel

K3K — Wed, 19 Sep 2018 22:29:31 GMT

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.

Re: importing from Excel

Tom — Wed, 19 Sep 2018 22:51:21 GMT

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.

Re: importing from Excel

K3K — Thu, 20 Sep 2018 13:12:31 GMT

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

Re: importing from Excel

Tom — Thu, 20 Sep 2018 13:35:10 GMT

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.

Re: importing from Excel

ballardw — Thu, 20 Sep 2018 16:18:28 GMT

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.