DATA Step, Macro, Functions and more

Import excel (Excel workbook type) File into SAS

Reply
Frequent Contributor
Posts: 97

Import excel (Excel workbook type) File into SAS

Hello friends

I want to import excel  (Excel workbook type) File   from a shared drive into SAS.

I used SAS import wizard  and then copy the code into a SAS program .

In the future I would like to use this code to import a new excel files that will come (files with same name).

 

The problem is that when I close SAS and Run it again then I get an error that the excel file is not exist.

In other words the problem is that SAS can't recognize the path in infile statement.

Why does it happen? What is the solution? How can I find the path?

 

Ron

PROC Star
Posts: 1,209

Re: Import excel (Excel workbook type) File into SAS

Is it out of the question to simply use PROC IMPORT without the import wizard?

Frequent Contributor
Posts: 97

Re: Import excel (Excel workbook type) File into SAS

I want to use data step to import because i need full control on varaibles
names and type and informat...
The question is why the path on infile statement is incorreft?
Sas is on linux environment i think
And the excel in on share drive
PROC Star
Posts: 1,209

Re: Import excel (Excel workbook type) File into SAS

what kind of excel file are you working with? .xlsx, .csv?

Frequent Contributor
Posts: 97

Re: Import excel (Excel workbook type) File into SAS

Excel workbook type (I think it is xlsx type)
Occasional Contributor
Posts: 10

Re: Import excel (Excel workbook type) File into SAS

did you try libname statement with XLS engine, there is one more engine I guess available. 
It will be very easy to deal with excel sheets. 
may be the following link could be helpful.

http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

Frequent Contributor
Posts: 97

Re: Import excel (Excel workbook type) File into SAS

Thank you so much.
It is first time i hear about excel libname.
What is the purose and advantage of it?
Occasional Contributor
Posts: 10

Re: Import excel (Excel workbook type) File into SAS

if you follow the link it will provide you more information. I have been using it frequently when I need to write data or read data from excel file. a  typical libname ( I am copying it again from the link I have mentioned above. )

 

libname mylib XLSX '/folders/myfolders/sas_tech_talks_15.xlsx'; 

 

 If there is any specific requirement let me know. following link can help you more : 
http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

Frequent Contributor
Posts: 97

Re: Import excel (Excel workbook type) File into SAS

Is it another way to import excel files?
What should be the path on the libname statement?
Super User
Posts: 13,283

Re: Import excel (Excel workbook type) File into SAS


Ronein wrote:

Hello friends

The problem is that when I close SAS and Run it again then I get an error that the excel file is not exist.

In other words the problem is that SAS can't recognize the path in infile statement.

Why does it happen? What is the solution? How can I find the path?

 

Ron


1. Show the code and error message from the log. Paste it into a code box opened using the forum {i} menu icon.

2. Verify the file is in the correct location, the name is actually the same (some OS are case sensitive) and the same type.

Be wary of people naming files data.xlsx but the actual file is HTML, CSV or XML (or possibly a few others).

3. Be prepared to have variable types and lengths change. The wizard most likely created proc import code. Proc Import has to GUESS what length and variable type, numeric or character, based on content. So the lengths of character variables may be different and some previously numeric will become character or vice versa.

 

When I deal with Excel files of the same structure I convert them to CSV and have data step code read them as then all the results have the same characteristics.

Frequent Contributor
Posts: 97

Re: Import excel (Excel workbook type) File into SAS

As I understand you recommended to save the excel files as csv comma delimited files (csv) and use data step to import them into sas.
There is still problem with path on infile statement.

Why each time i use import wizard (that create import code in data step version) i get different paths??
Is it because sas run in linux and not windows?
Super User
Posts: 13,283

Re: Import excel (Excel workbook type) File into SAS


Ronein wrote:
As I understand you recommended to save the excel files as csv comma delimited files (csv) and use data step to import them into sas.
There is still problem with path on infile statement.

Why each time i use import wizard (that create import code in data step version) i get different paths??
Is it because sas run in linux and not windows?

And you have not shown the log with the code and the error.

 

If you are working on windows with a linux server then the Server does not see you computer disk as one of the server's disks where it has read access. The graphic interfaces actually use more behind the scenes activity than submitted code to achieve this task. For code to find a file then the file needs to be someplace, possibly a shared drive, that both computers have access to.

Occasional Contributor
Posts: 10

Re: Import excel (Excel workbook type) File into SAS

I did not understand. Could you please just tell me which SAS client are you using ?
Are you connected to correct metadata sas server ?
Where is the file located ?
If it is located on Windows machine then try ftp client to transfer it on server and then try again.
If still you are not able to access it. Please write the procedure with screenshot. I will assist you.
Frequent Contributor
Posts: 97

Re: Import excel (Excel workbook type) File into SAS

I will be at work on Sunday and will explain more.
As I said excel file is located on a shared network.
Super User
Super User
Posts: 7,929

Re: Import excel (Excel workbook type) File into SAS

Sounds like you are using Enterprise Guide.  That program has a utility that will convert your Excel file into a text file and then upload the text file and a program that it creates to read the text file.  So you cannot re-use the SAS program for a new file since you will not have run the steps in EG that created the text file that the progam expects.

 

Ask a Question
Discussion stats
  • 16 replies
  • 275 views
  • 0 likes
  • 5 in conversation