topic Re: FTP XLSX in SAS Programming

FTP XLSX

SASUser_86 — Wed, 20 Nov 2019 15:18:17 GMT

Hi. I want to ftp a a dataset in xlsx format.

I'm using the following code, but keep receiving error:

%let ftpUID=username;

%let ftpPWD=password;

%let outputFilename=file1;

filename datasetname

ftp cd= "file1"

host="inserthost"

user = "&ftpUID."

pass = "&ftpPWD."

dir

debug;

data _null_;

file dim_usr_ofc(&outputFilename..xlsx);

termstr=crlf;

run;

Error:

ERROR: Invalid logical name.
ERROR: Error in the FILENAME statement.

Any advice is appreciated. Thank you.

Re: FTP XLSX

novinosrin — Wed, 20 Nov 2019 15:23:13 GMT

For all I know, datasetname is a fileref with more than 8 characters which does not conform to the rules of length of fileref. Is this something to do with the error stated?

Re: FTP XLSX

SASUser_86 — Wed, 20 Nov 2019 15:25:54 GMT

Hi Novinosrin. Is the filename supposed to be the name of the sas dataset I want to export to the FTP server?

Sorry, I am very new to SAS. I don't quite understand most of the terminologies.

Re: FTP XLSX

Kurt_Bremser — Wed, 20 Nov 2019 15:30:29 GMT

File and library references must not be longer than 8 characters. That's still a hard limit in SAS.

Change your local file reference to something shorter:

filename dsname
  ftp
  cd= "file1"
  host="inserthost"
  user = "&ftpUID."
  pass = "&ftpPWD."
  dir
  debug
;

Re: FTP XLSX

SASUser_86 — Wed, 20 Nov 2019 15:57:15 GMT

Is the filename supposed to be the name of the sas dataset that I want to be moved in xlsx format to the ftp location? Thank you very much.

Re: FTP XLSX

Tom — Wed, 20 Nov 2019 16:03:17 GMT

You use the FILENAME statement to create essentially an alias you want to use to reference a file. We normally refer to this alias as the FILEREF. That is what needs to be 8 characters or less. It does not have to have anything to do with the name of the actual file you are referencing. In your code you trying to use DATESETNAME as the fileref. That is too long. Use something shorter.

Re: FTP XLSX

Kurt_Bremser — Wed, 20 Nov 2019 16:08:06 GMT

When you run the filename statement, you create a logical reference (for use only within SAS) that stands in for something more complicated (e.g. a ftp connection).

So if you do this:

filename out
  "myexcelfile.xlsx"
  ftp
  cd= "file1"
  host="inserthost"
  user = "&ftpUID."
  pass = "&ftpPWD."
  dir
  debug
;

SAS will set up the reference, but not yet really do anything.

Then, when you later use that reference:

proc export data=yourdata file=out dbms=xlsx;
run;

SAS will read the SAS dataset work.yourdata, convert it to the xlsx format, open a connection to inserthost, log on with the credentials supplied, do a change directory to directory file1, and create myexcelfile.xlsx there, with the converted dataset as contents.

Note that you cannot create an Excel file with a data step; only text files (e.g. .csv) can be written with a data step.

Re: FTP XLSX

ballardw — Wed, 20 Nov 2019 16:35:47 GMT

@SASUser_86 wrote:
Is the filename supposed to be the name of the sas dataset that I want to be moved in xlsx format to the ftp location? Thank you very much.

Maybe this will help a bit:

File: the actual path and name on a hard drive. Example on Windows: C:\somefolder\otherfolder\thisfile.extention

FILEREF: a short name, max 8 characters that allows you to reference that file with something like: Myfile

FILENAME a statement in SAS that associates the Fileref with the File:

Filename Myfile "C:\somefolder\otherfolder\thisfile.extention" ;

So in SAS procedures where you want to use the file you use the shorthand Fileref after the Filename statement does the association.

A Fileref could be used as a reference for reading, writing or (very carefully) both

Generally you would not want a fileref for the SAS data set name. SAS keeps track of data sets in Librarys and the sets are referenced as LIBNAME.Dataset, with the WORK library the default if no explicit library is used. Libraries are locations storing multiple items such as data sets and a LIBNAME statement creates a LIBREF, again 8 character limit, that points to the location.

Re: FTP XLSX

SASUser_86 — Wed, 20 Nov 2019 16:40:16 GMT

I used the code you provided. Based on your explanation I also realized that I misunderstood the cd portion of the code. I want to store the excel file in the root directory, so I made the change to "/". I have also include export data into my code. Please see errors below.

%let ftpUID=username;
%let ftpPWD=password;
%let outputFilename=file1;
filename out
  "myexcelfile.xlsx"
  ftp 
  cd= "/"
  host="hostname"
  user = "&ftpUID."
  pass = "&ftpPWD."
  dir
  debug
;

proc export data=mylib.test_bp file=out dbms=xlsx;
run;

Re: FTP XLSX

Tom — Wed, 20 Nov 2019 16:49:09 GMT

In a FILENAME statement the ENGINE (if specified) must appear immediately after the FILEREF.

filename filef [engine]  .... ;

In your case you want to use the FTP engine.

Are you sure that PROC EXPORT can even create an XLSX file using the FTP engine? You might have to create the XLSX file as a physical file on your SAS system first and then copy it to the FTP server.

Re: FTP XLSX

Kurt_Bremser — Wed, 20 Nov 2019 17:34:59 GMT

@Tom wrote:

Are you sure that PROC EXPORT can even create an XLSX file using the FTP engine?

I guess so (will test it tomorrow). The file reference handles the transport, while proc export handles the conversion. For the file reference, it only receives a stream of data and puts it somewhere, while proc export creates that stream and does not worry what happens afterwards.