topic Re: Reading data from a FTP site (.XLS file) in SAS Enterprise Guide

Reading data from a FTP site (.XLS file)

Jackme — Mon, 05 Jan 2015 20:28:12 GMT

Hello All

I have a program which is running fine when I run local . We wan to schedule this program to automate the data sets. I'm not able to get all the field values correctly .

We have a .XLS file on a FTP server , we need to read data from that server using SAS enterprise guide create a SAS , using this in other programs.

Here is the sample and issue details..

FILENAME TEST11 FTP 'mydata.xls' lrecl=256

CD='/EEXInterfaces/Data/'

HOST='myhost'

USER='anonymous'

PASS='XXXX' ;

DATA REC11 ;

INFILE TEST11;

INPUT Region $9.;

RUN;

PROC PRINT DATA=REC11;

RUN;

It's not printing values from region column in that excel. I have other columns also ..I think this is related to converting binary format to correct format which SAS can understand . Any idea .. It's urgent ..

Rgds

Jagadish

Re: Reading data from a FTP site (.XLS file)

jakarman — Mon, 05 Jan 2015 20:53:10 GMT

more ideas than being sure on that as you gave too little information.

- The FTP protocol is doing sometimes unwanted conversions. Usually related to crossing different systems (Unix/Windows)

- The xls file you are reading onlys is readable with a datastep when it is really more like a csv-file

- running in batch is ususally done with different credentials they could be blocked for FTP (security)

All kind of combinations of these with variations.

What did run in EGuide this code?

Re: Reading data from a FTP site (.XLS file)

Jackme — Mon, 05 Jan 2015 20:57:57 GMT

Thanks. As per my knowledge nothing is blocked to retrieve data from FTP site. Yes the same code (adding actual location of the file)I ran in EG . I see there is a problem in displaying that data once it's read from XLS.

Any idea / thoughts..

Re: Reading data from a FTP site (.XLS file)

jakarman — Mon, 05 Jan 2015 21:55:27 GMT

And the code in EG is giving the error?

- Checked whether it is CSV type named as XLS (that could work) or is the binary 2003 Excel type or the zipped/xml one known as .xlsx?
- Checked the possible translations in FTP? (binary and no record separation are often special parms)
You could do a binary download and then convert the file as usual. Two steps but more easy to validate.

Re: Reading data from a FTP site (.XLS file)

ballardw — Tue, 06 Jan 2015 00:00:37 GMT

I second Jaap's approach of downloading the file. You may need it for documenting when format changes occur such as colum order changes/additions/deletions or trouble shooting why expected numeric values are character or even possible text encoding issues.

Re: Reading data from a FTP site (.XLS file)

Jackme — Tue, 06 Jan 2015 02:27:09 GMT

No errors , but data displayed is not correct . I want to see the data which is there on the first column ( Region) but it's displays some values from other columns.

It's .xls format not CSV shows as xls. Can you please let me know the code how we can download binary and covert to file as usual.

I'm new to EG

Re: Reading data from a FTP site (.XLS file)

jakarman — Tue, 06 Jan 2015 06:28:45 GMT

this is one approach Copy a file using a SAS program: another method - The SAS Dummy

reading form maianfrem binary eplains a lot on different machines using (s)ftp. TS-DOC: TS-642 - Reading EBCDIC Files on ASCII Systems

When being allowed to used xcmd you could uses the often mandatory standard tools local in place.

Many more options as being a standard way. As you mentioning anonymous Is it public data (we could try) or local in house data?

Re: Reading data from a FTP site (.XLS file)

gergely_batho — Tue, 06 Jan 2015 07:30:08 GMT

The above code was working locally with the same file? What was exactly the code?

Stange, that you can read an Excel file using a data step.

What doeas "run local" mean?

Do you have SAS installed on your (Windows?) desktop? Or you have only EG (so you were connected to a SAS server)? Is the server Windows? Do you have ACCESS to PC file on the server<

If this is real Excel data (not csv), use ftp binary transfer:

FILENAME TEST11 FTP 'mydata.xls' lrecl=256 binary

CD='/EEXInterfaces/Data/'

HOST='myhost'

USER='anonymous'

PASS='XXXX' ;