BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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

16 REPLIES 16
PeterClemmensen
Tourmaline | Level 20

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

Ronein
Meteorite | Level 14
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
PeterClemmensen
Tourmaline | Level 20

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

Ronein
Meteorite | Level 14
Excel workbook type (I think it is xlsx type)
cpagrawal
Fluorite | Level 6

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/

Ronein
Meteorite | Level 14
Thank you so much.
It is first time i hear about excel libname.
What is the purose and advantage of it?
cpagrawal
Fluorite | Level 6

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/

Ronein
Meteorite | Level 14
Is it another way to import excel files?
What should be the path on the libname statement?
ballardw
Super User

@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.

Ronein
Meteorite | Level 14
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?
ballardw
Super User

@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.

cpagrawal
Fluorite | Level 6
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.
Ronein
Meteorite | Level 14
I will be at work on Sunday and will explain more.
As I said excel file is located on a shared network.
Tom
Super User Tom
Super User

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.

 

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
  • 16 replies
  • 1436 views
  • 0 likes
  • 5 in conversation