DATA Step, Macro, Functions and more

Import Excel file into SAS

Reply
Contributor
Posts: 24

Import Excel file into SAS

Hello

I am using SAS Enterprise.

I am trying to import  Excel file (Xlsx) into SAS enterprise.

The excel File contain only one spreedsheet .

The columns headers of excel file are not in Egnlish and the user who created the excel file cannot change it to English.

 

I use import wizard to import the excel file and SAS print the code in the lof window.

I took the code and changed the columns names to English and all is fine and suceesfully imported the excel file.

 

The problem is that when I use the code again (Next day or when I close SAS and open SAS again)  then it won't work.

The error is "physical file does not exist"

 

It is a big problem because my client should Run the code in daily base and just upload a new excel file with same name everyday.

 

Can you please suggest a solution?

Thanks

 

 

Frequent Contributor
Posts: 95

Re: Import Excel file into SAS

Some things you might like to try have been discussed here:

 

https://communities.sas.com/t5/General-SAS-Programming/ERROR-Physical-file-does-not-exist/td-p/11200...

Norman.
SAS 9.4 (TS1M4) X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

Super User
Posts: 7,845

Re: Import Excel file into SAS

If you used the import wizard in a client-server environment, then the whole process has two stages: EG converts the Excel to text and uploads that, and then executes code that imports the text to a dataset. No stage works without the other.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,855

Re: Import Excel file into SAS


Ronein wrote:

Hello

I am using SAS Enterprise.

I am trying to import  Excel file (Xlsx) into SAS enterprise.

The excel File contain only one spreedsheet .

The columns headers of excel file are not in Egnlish and the user who created the excel file cannot change it to English.

 

I use import wizard to import the excel file and SAS print the code in the lof window.

I took the code and changed the columns names to English and all is fine and suceesfully imported the excel file.

 

The problem is that when I use the code again (Next day or when I close SAS and open SAS again)  then it won't work.

The error is "physical file does not exist"

 

It is a big problem because my client should Run the code in daily base and just upload a new excel file with same name everyday.

 

Can you please suggest a solution?

Thanks

 

 


 

Does the code work the same day with the same file?

If not, then the code isn't valid for some reason. Assuming 'Enterprise' is SAS EG then the file likely needs to be on the server so you likely have the path incorrect. You can move the file to the server or set up a shared folder where your client can drop the file and SAS EG can access the file. Talk to your IT administrator on how to set this up.

Contributor
Posts: 24

Re: Import Excel file into SAS

The excel file as same name every day and just the data is different every day.
The client will upload every day a new file.
The excel file is on a shared drive .
I didnt understand how IT admin can help me?
Please explain what action is required?
Contributor
Posts: 24

Re: Import Excel file into SAS

Thank you but how your information vam help me to solve the problem?
What action is required that the client will the import code in daily base without error that phsical file doesn't exist
Super User
Posts: 7,845

Re: Import Excel file into SAS


Ronein wrote:
Thank you but how your information vam help me to solve the problem?
What action is required that the client will the import code in daily base without error that phsical file doesn't exist

If the file resides on a drive that the SAS server can see, and only a portion of the filename changes each day, you can do the following:

- use the code that you have that works for the current file

- create a user prompt for the variable part of the filename

- use the result of the user prompt in the filename. User prompts are supplied to the code as macro variables, with the name of the prompt

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,845

Re: Import Excel file into SAS


Ronein wrote:
Thank you but how your information vam help me to solve the problem?
What action is required that the client will the import code in daily base without error that phsical file doesn't exist

But if you imported the file with the import wizard, my earlier post is valid, and the code from that won't work on its own without the conversion/file transfer that EG does. For that to work, the customer must also use EG, and you have to give them the project, and not just the code.

Consider writing code that uses proc import directly on the Excel file, if the SAS server can "see" the shared location.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 145 views
  • 0 likes
  • 4 in conversation