Hi All,
I`m trying to read an excel file from FTP. I cannot succeed in doing so. following is the code I use,
filename xlremote ftp 'test.xlsx' host='ftp.mycompany.com' cd='/somepath/path/' user='username' pass='password' debug;
proc import datafile=xlremote out=test dbms=excel replace; run;
I get the following error when I run the above code
ERROR: This "filename FTP" access method is not supported by "proc import". Please copy the file to local disk before running the procedure.
Thanks,
Saravanan
Do as you are told. Copy the file externally (use a scripted ftp with the x statement), and then run proc import.
I think that proc import makes use of Microsoft Data Access Objects which need to see the file locallly.
I`m sorry. I do not understand it. can you please provide me a sample code? Thanks.
On which platform are you using SAS?
Windows ftp does have its quirks compared to "standard" ftp on a UNIX platform.
I`m working on Windows platform. I`m looking to read the file from FTP directly or download the file using SAS to local system.
begin with:
data _null_;
file 'C:\temp\ftp.script';
put "open ftp.mycompany.com";
put "username"; /* supply the username in plain text here */
put "password"; /* supply the password in plain text here */
put "cd /somepath/path";
put "lcd where-you-want-the-file-to-end-up"; /* (eg c:\temp) */
put "bin";
put "get test.xlsx";
put "quit";
run;
x "ftp -s c:\temp\ftp.script";
x "del c:\temp\ftp.script";
the script file is necessary because it's (next to) impossible (AFAIK) to pipe the commands into the windows ftp.exe
now you can run the proc import on c:\temp\test.xlsx
edit: had to change how user and password are supplied, added "bin"
I tried running this piece of code. Below are the errors I got. I believe these errors are due to missing ftp.script file? if yes, how can I go about writing the script file? example code would be great as I have no experience in writing scripts.
ERROR: Physical file does not exist, C:\temp\ftp.script.
ERROR: Shell escape is not valid in this SAS session.
x "ftp -s c:\temp\ftp.script";
ERROR: Shell escape is not valid in this SAS session.
x "del c:\temp\ftp.script";
Kurt assumption is correct. The current implementation of the SAS-Excel interface is using microsoft ACE driver.
By that the file needs to be local present on a Windows-machine. ACE has several limitations as originating from excel 2003.
That is is bit shamefull as the standard of xlsx is essential a Zip-file.containging some Xml files and more files. There is no need for using ACE anymore.
The Excel type xlslx is well documented at OASIS.
or copy file binary using SAS
I just suspect that reading a file byte-wise may be slow as drying paint
Kurt agree, Now it is hoping for not too big data and efficient buffering/caching.
A better approach would by 4K records except the last one (variable) connecting to TCP-IP buffering
The best one would be not being dependent on Microsoftdrivers like ACE. Direct reading the xlsx file.
The best performance on IO time is not having (or as less as reasonable possible) IO.
Saravan, is your SAS session running under Windows? We would have expected that.
The messages (shell escape) are giving the impression you are on a server-side probably a Unix version
As of sas 9.3 the default would be that x-commands are allowed on you windows SAS desktop
Hi Jaap,
I am running EG5.1 on my client desktop which is Windows 7 professional, I connect to SAS 9.3 housed on a server which is running on Windows server 2008 R2 enterprise. Am I missing something?
In this case, you can either make sure that the windows server has access to the excel file via AD, or use the ftp transfer as in the example I gave you.
But to use ftp you first have to modify the default behaviour of the SAS workspace server that does all the work for your Enterprise Guide.
By default, no shell escapes are allowed in workspace server sessions right out of the box.
I hope this works for you (did that quite some time ago, SAS 9.2):
Start Management Console and log in as sasadm when connecting to the metadata server.
Search for your workspace server definition:
Plug-Ins - Server Manager - SASApp (the name you see as server in EG) - Logical Workspace Server - Workspace Server
Right Click on Workspace Server, select Properties and then Options, you will see the script that is used to start the server instance. Click on Advanced Options (or whatever it is called, it is "Erweiterte Optionen" in German). In Startup Properties you need to activate "allow XCMD")
To put this into effect, you then have to restart the Object Spawner on this machine, you can do this via the MC
Well we were missing something not knowing your environment.
SAS is not running on your desktop, the view URLs autorisations are not necessary the same.
Running Microsoft interfaces on the server are requiring they are present on that side.
Eguide has good interfaces to the desktop but should be seen as a different approach.
Do you have more details on your issue?
Who is your platform administration at the server?
Does he accept SAS or is it something of an other world for him.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.