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
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?
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..
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.
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
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.
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?
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' ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.