topic Re: SFTP with current date in filename in SAS Enterprise Guide

SFTP with current date in filename

brighterlight — Wed, 04 Jul 2018 08:24:25 GMT

data trial;
input filename $ number $;
datalines;
testtest 999
;
run;

/*code 1*/

%ds2csv(data=trial, colhead=N, runmode=b, csvfile=D:\testing2\&date.file_trial1.csv);


/*code 2*/

filename outfile1 sftp '/c:/dummy/&date.file_trial1.csv' USER="dummy_123" host="821.12.23.321" optionsx= "-i d:\dummy_key.ppk -pw testing000" DEBUG;
PROC EXPORT Data = test trial= outfile1 DBMS=csv REPLACE;run;
run;

Hi all,

Would require help in this.

my code works for code 1, which means the file saved in my d drive is 180704.file_trial1.csv

while the file saved for code 2 is &date.file_trial1.csv

Would kindly seek assistance on how to pass current date over to that file that requires to be sftp over.

Thank you.

Re: SFTP with current date in filename

RW9 — Wed, 04 Jul 2018 08:44:29 GMT

You need to put double quotes around macro variables to get them to resolve:

filename outfile1 sftp "/c:/dummy/&date.file_trial1.csv" USER="dummy_123" host="821.12....

You could also simplfy by removing the &date macro variable and using a function:

filename outfile1 sftp "/c:/dummy/%sysfunc(date(),date9.)_file_trial1.csv" USER="dummy_123" host="821.12....

However, I recommend not putting data in filenames, data should go in the data area as this makes further processing far simpler, and doesn't break version control systems and such like. For instance, we refuse to accept dated, version number or any such thing in files, they should all have the same name so the version control system can keep the history, and out users do not need to code for this.

Re: SFTP with current date in filename

brighterlight — Wed, 04 Jul 2018 10:00:14 GMT

Hi thanks for the reply. Unfortunately, I have tried it before as well, it doesn't work.

Re: SFTP with current date in filename

RW9 — Wed, 04 Jul 2018 10:16:35 GMT

"it doesn't work" - this does not tell us anything.

Re: SFTP with current date in filename

brighterlight — Thu, 05 Jul 2018 01:47:37 GMT

The file that was SFTP over was %sysfunc(date(),date9.)_file_trial1.csv

There wasn't any error so there's nothing much I can mention about it.

Re: SFTP with current date in filename

FredrikE — Thu, 05 Jul 2018 07:40:46 GMT

Hi!

I think it is easier to create the file name in a data step first. In that way you can check everything before defining the filename.

data createdFilename;
date = put(date(),date9.);
filename = cats(date,'_file_trial1.csv');
call symput('_fn',filename);
run;

%put &_fn;

filename outfile1 sftp "&_fn" USER="dummy_123" host="821.12.23.321" optionsx= "-i d:\dummy_key.ppk -pw testing000" DEBUG;
PROC EXPORT Data = test trial= outfile1 DBMS=csv REPLACE;run;
run;

//Fredrik

Re: SFTP with current date in filename

brighterlight — Thu, 05 Jul 2018 08:56:14 GMT

Hi Fredrik,

Thanks but this will not work either because a location is need, the statement which you have after sftp excludes the location and I need the file name as well.

Re: SFTP with current date in filename

FredrikE — Thu, 05 Jul 2018 11:12:19 GMT

uhum, ok, but then you just add it to the _fn variable in the datastep or what?

The thing with the macro variable is that it can contain anything you need, passwords, paths, options and so on,...:)

//Fredrik