Desktop productivity for business analysts and programmers

Need Help with Reading a CSV file in SAS EG (from SFTP site)

Reply
Occasional Contributor
Posts: 16

Need Help with Reading a CSV file in SAS EG (from SFTP site)

Hi there,

Sounds trivial? 

but I have been having problems reading files

If I invoke the drop down fucntionality unders files -> import data and point to a location and try reading the files I have no problems and it generates the following code:


DATA WORK.test_for_sas2;
    LENGTH
        name             $ 3
        acc_no             8 ;
    FORMAT
        name             $CHAR3.
        acc_no           BEST1. ;
    INFORMAT
        name             $CHAR3.
        acc_no           BEST1. ;
    INFILE 'E:\saswork\_TD7416\#LN00368'
        LRECL=5
        ENCODING="WLATIN1"
        TERMSTR=CRLF
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        name             : $CHAR3.
        acc_no           : ?? BEST1. ;
RUN;

To me it seems that SAS internally copies the file to our SAS server (EG) and reads it in, but If i change the infile statement from above to  point to server location where I want to read from (for automation purposes)

e.g. infile   '\\sdax.server.ca\sftp_read\file.csv'  it gives me an I/O error. I am stumped.

This server location is an SFTP folder for our vendors to drop the csv. I want to automate the process of reading the files, but can't do so.

Would you have an idea , how to read it in? I can't use windows shell command to copy (as this functionality in SAS EG is disabled and our admin won't allow this) the files to another location and read it in. BTW if I copy this file to our SAS EG server location and point to that location using infile statement, SAS works and reads the file, So I am guessing it is some sort of the settings on this folder /server which is preventing SAS to read.

Please help ?

Thanks,

PD

Trusted Advisor
Posts: 2,114

Need Help with Reading a CSV file in SAS EG (from SFTP site)

PD,

I don't know the "solution" to your problem, but I do have a workaround that I have used.

I use the system scheduler (in Windows XP, it is "Scheduled Tasks" in the Control Panel) to copy the file from the external site to a file structure that I can get to (you can run your FTP commands through a .BAT file).  Then I have another scheduled task to go off a few minutes later to run the EGuide or batch SAS part.

Doc Muhlbaier

Duke

Super User
Super User
Posts: 6,842

Need Help with Reading a CSV file in SAS EG (from SFTP site)

You need to talk to your IT support about allowing your SAS server to access the folder that you are accessing from your PC with Enterprise Guide.

If it really is an FTP server then try using the FTP engine on the FILENAME statement (or the INFILE statement).

Occasional Contributor
Posts: 7

Re: Need Help with Reading a CSV file in SAS EG (from SFTP site)

Remove TERMSTR=CRLF option from infile statement. I hope it will work. I had a same issue and it worked for me.

Contributor Dil
Contributor
Posts: 23

Re: Need Help with Reading a CSV file in SAS EG (from SFTP site)

hi,

I had the exactly same problem today. When I imported the csv file manually, it was fine and it created similar codes as yours. However, when I used the proc import, it is giving me errors for two of the numeric variables, which I double checked that they don't have any unusual values or any symbols. Need to figure this out too.

Super User
Posts: 9,854

Re: Need Help with Reading a CSV file in SAS EG (from SFTP site)

Maybe you need add another option   guessingrows=32767   to avoid such error when you are using proc import for CSV file.

Contributor Dil
Contributor
Posts: 23

Re: Need Help with Reading a CSV file in SAS EG (from SFTP site)

Thanks Xia Keshan. I didn't have any problem. when I used guessingrows=32767 option. The thing is all the numeric variables including sas date variables all became character variables. Any suggesion on resolving this issue? Thanks.

Community Manager
Posts: 2,882

Re: Need Help with Reading a CSV file in SAS EG (from SFTP site)

You can use this method to copy a file from anywhere a FILENAME statement can reference: FTP, HTTP, and SFTP.

You won't need to run additional shell commands or FTP scripts.  For SFTP, you will need a OpenSSH or PUTTY (depending on Windows or Unix) to be configured for your SAS environment.  See the FILENAME SFTP access method documentation.

Chris

Super User
Posts: 7,378

Re: Need Help with Reading a CSV file in SAS EG (from SFTP site)

At least in 9.2 on AIX, filename SFTP does not work reliably. SAS routinely tries to read past the end of the temporary file; it basically "overtakes" the file transfer process.

I had to run the file transfer manually via X and then read the copied file.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 3,208

Re: Need Help with Reading a CSV file in SAS EG (from SFTP site)

>>> INFILE 'E:\saswork\_TD7416\#LN00368'

This is an automatic naming of a temporary file being uploaded to the server. If this is your remote server your SAS server is a Windows machine.

As the used local saswork is a temporary one you will get a different name the next time.

That code is useless to get automated.

>>> infile   '\\sdax.server.ca\sftp_read\file.csv'    ???

That is not ftp acess method is a URI name in your Windows domain.  You are probably referring to a location where text files using a sftp-tool are dropped for you. 

Most SAS installations are not setup to be part of the AD domain. Your SAS-platform admin should cooperated with AD windows and central identity security.

Only in that way you could define the authorization settings getting all the data in the AD domain accessible.    

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 9 replies
  • 1853 views
  • 1 like
  • 9 in conversation