BookmarkSubscribeRSS Feed
Jackme
Calcite | Level 5

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

7 REPLIES 7
jakarman
Barite | Level 11

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? 

---->-- ja karman --<-----
Jackme
Calcite | Level 5

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..

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
Jackme
Calcite | Level 5

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

ballardw
Super User

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.

jakarman
Barite | Level 11

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?    

---->-- ja karman --<-----
gergely_batho
SAS Employee

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' ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3491 views
  • 0 likes
  • 4 in conversation