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
Is it out of the question to simply use PROC IMPORT without the import wizard?
what kind of excel file are you working with? .xlsx, .csv?
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/
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 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 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.
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.
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.