topic Re: Store Filename as Column while importing multiple files from FTP server in New SAS User

Store Filename as Column while importing multiple files from FTP server

shubh1996 — Tue, 25 Apr 2023 18:47:26 GMT

So I am currently importing multiple text files from an FTP server into a single dataset. Due to the nature of the text files, I have to manually define the column lengths and names. What I am unable to figure out is how do I store the filename as a variable and insert it as a column so that the data for each file has the corresponding file name. The FTP I am using in WinSCP, and the files are in a subdirectory, where the prefix of the file is constant, but the suffix is changed based off of the timestamp that it gets uploaded. Currently my code looks like this (generalized)

filename in ftp 'SUBFOLDER/common_file_prefix.*' /* The suffix is a variable name that has the timestamp of when the data was uploaded*/
user = 'user' 
password = 'password'
host = 'host'
mget debug;

DATA project_data;
INFILE IN TRUNCOVER;
INPUT
      COL1 $0-10
       ...
      COL 30 $500-550
RUN;

I tried to add a column at the end using FILE_NAME = fname but that did not work, I have also tried to use the FILENAME = option I haven't gotten it to work for me yet either.

Re: Store Filename as Column while importing multiple files from FTP server

Kurt_Bremser — Tue, 25 Apr 2023 19:46:58 GMT

DATA project_data;
length file_name fname $200;
INFILE IN TRUNCOVER filename=fname;
INPUT
      COL1 $0-10
       ...
      COL 30 $500-550
;
file_name = fname;
RUN;

Re: Store Filename as Column while importing multiple files from FTP server

ballardw — Tue, 25 Apr 2023 19:51:46 GMT

When code doesn't work but you think that it should, then show us the log with the code and any messages received.

Copy the text from the log and then paste that into a text box opened on the forum.

The Infile statement FILENAME= option creates a temporary variable. If you want the result in the output then you need to define another variable long enough to hold expected values and assign the value to that variable.

Something like this: But I haven't used this with FTP file method and different FTP servers may behave / differently.

DATA project_data;
INFILE IN TRUNCOVER filename=infile;
INPUT
      COL1 $0-10
      <rest of input statement>
;
length Source $ 300;
source = infile;
run;

Re: Store Filename as Column while importing multiple files from FTP server

shubh1996 — Tue, 25 Apr 2023 19:58:33 GMT

Neither of the methods have worked, SAS is able to recognize the filename in the LOG, but for some reason it doesn't seem to be getting stored.

@Kurt_Bremser

Re: Store Filename as Column while importing multiple files from FTP server

ballardw — Tue, 25 Apr 2023 21:10:45 GMT

Logs really should include the actual submitted code so we actually see the options you submitted.

From the documentation of Filename FTP

MGET

transfers multiple files, similar to the FTP command MGET.

Tips The whole transfer is treated as one file. However, as the transfer of each new file is started, the EOV= variable is set to 1.

Specify MPROMPT to prompt the user before each file is sent.

I think this means that the FILENAME= infile option is only going to get one string, content unknown.

You might use the EOV= option to set a file counter in a retained variable and then use a separate connection to get this of files then match them up. Maybe.

data example;
   infile in <other options> eov=newfile;
   retain filecount;
   if newfile then filecount+1;
run;

And then use a separate FTP command to execute the LIST command. Look at examples for "Retrieving a Directory Listing"

I don't know if it will help and it has been a while since I actually used FTP much but perhaps if instead of

filename in ftp 'SUBFOLDER/common_file_prefix.*' /*

That you connect to the site and then use the CD= option to move your connection point to the SUBFOLDER directory.

Somewhat of a shot in the dark but it might be that the information passed to include the SUBFOLDER from the connection isn't.

The size of your files appears to be relatively small. Perhaps just use MGET to download all of the files to a local drive and then the INFILE with Filename= shouldn't have any problem.

Re: Store Filename as Column while importing multiple files from FTP server

Kurt_Bremser — Wed, 26 Apr 2023 05:09:18 GMT

In order to get the filenames, retrieve the directory first, then read each file individually.

The second can be done in a single data step, where you read the filenames from the dataset containing the directory listing, use the FILEVAR= option in the INFILE statement, and a DO loop to read the file.

My presentation https://communities.sas.com/t5/SAS-User-Groups-Library/WUSS-Presentation-Talking-to-Your-Host/ta-p/838344 contains an example where I do this with INFILE PIPE, you need to try this with INFILE FTP.

Tips	The whole transfer is treated as one file. However, as the transfer of each new file is started, the EOV= variable is set to 1.
	Specify MPROMPT to prompt the user before each file is sent.