01-05-2015 03:28 PM
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
DATA REC11 ;
INPUT Region $9.;
PROC PRINT DATA=REC11;
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 ..
01-05-2015 03:53 PM
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?
01-05-2015 03:57 PM
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..
01-05-2015 04:55 PM
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.
01-05-2015 09:27 PM
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
01-05-2015 07:00 PM
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.
01-06-2015 01:28 AM
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?
01-06-2015 02:30 AM
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