Macro Variable Resolution Question

[LionelT]

We currently use the FTP option on the Filename statement a great deal in interacting with that Big piece of Iron called a Mainframe. We have multiple routines that read, write and otherwise take full advantage of the FTP and Filename tools within SAS.

In the simplest of cases our Filename statements use a macro variable to resolved the Host= keyword. We have stored the macro variable in a file containing various other key values that we use. The variable currently simply provides the Host id. It is similar to this.

%LET HOSTFTPID              =BIGFTP.COMPANY.COM ;

Then in the file name statement we have a line such as…

FileName ADVINT00  FTP "'BIGHOSTFILENAMEHERE'" Host="&HOSTFTPID" ;

We are moving to an SFTP server and we are now required to add two other options to our filename statements along with changing the Host= variable. I was hoping to do this with a single elegant change of the %let statement to the following

%Let HOSTFTPID =BIGSFTPServer.COMPANY.COM port=1065 Prot=P 

Sadly, it appears that when it comes to resolving the macro the full value is being inserted into the 'Host=' piece and I am getting an error message that notes that the host name is invalid and upon inspection we can see that the additional options are being included within the Host= command itself.

ERROR: Open failed for file ADVINT00

ERROR: Host name BIGSFTPServer.COMPANY.COM port=1065 Prot=P not found.

I am wondering if any quick solution is present to allow the port and prot options to be separated within the macro variable and hence preventing the unfortunate next step, being to add the Port and Prot options to the myriad number of filename statements!

[PaigeMiller]

First step before you use macro variables is to get the code correct without macro variables.

So, show us working SAS code without macro variables. Then we can help you (it should be easy) turn this into code that works with macro variables.

Typically, people ignore this valuable advice and continue on as if the advice hadn't been given. Do not do that. It will help you (and help us help you) in the long run.

[LionelT]

Hi Paige.

Log Snippets from a working process. Modified to remove any sensitive stuff.

39 %Let TDMFURL =BIGSERVER.INTHESKY.COM ;

42 Filename Detail FTP "'FIPP.C.D.FPDAT390.ADVISOR.INTEREST(0)'" Host="&TDMFURL"
42 ! port=1065 Prot=P
43
44 recfm=FB lrecl=90
45 ;
46
NOTE: 220-TCPSFTP1 IBM FTP CS V2R3 at BIGSERVER.INTHESKY.COM, 08:38:36 on 2022-03-15.
NOTE: User TSOUSER has connected to FTP server on Host BIGSERVER.INTHESKY.COM .
NOTE: The infile DETAIL is:
Filename='FIPP.C.D.FPDAT390.ADVISOR.INTEREST(0)',

[PaigeMiller]

Do you not see the difference between your original attempt in your first code, and this new code that does work? This is why it is so critical to create code that does work before creating macro variables.

In this new code, which appears to work properly, this is not inside the double quotes

port=1065 Prot=P recfm=FB lrecl=90

So you can't put this inside double quotes, that's why your first attempt failed.

[LionelT]

Thanks for your note Paige.

The desire was to see if it is possible to update the TDMFURL macro variable to a value that would allow our process now include the new required options without having to update any Filename statements in our downstream routines. But that sadly does not appear to be the case. It did provide me though with an opportunity to reflect on SAS Macro variables a bit more than I have in the past.

[Quentin]

Yes, Tom came very close to giving you what you had hoped for, but since your real code has additional options at the end of the statement, I don't think you'll be able to avoid updating your code.

As a macro design issue, I've often debated whether it's a good practice to store values in macro variables / macro parameters with quotes as part of the value or not.  Clearly this is a case, like many, where storing the quotes as part of the value gives you flexibility to add non-quoted text.

[Quentin]

Building off of @Tom 's thought, perhaps there is a filename statement option which accepts a null string as an argument, that might allow you to "eat" the extra quote mark without changing your main code.  Something like (untested):

data _null_;
  call symputx('HOSTFTPID','BIGSFTPServer.COMPANY.COM" port=1065 Prot=P termstr="');
run;

So that would add an unneeded termstr="" to the middle of your filename statement.  If there's an option like that which would not break things, it may be a way out.

[LionelT · Accepted solution]

So..... a super special thanks to all of you who replied. Thanks a ton!

Tom started me off down a very good path and then quentin nudged the thoughts a bit with using an alternative option available that required 'quotes'. After playing around just a bit with it (I am doing this thing on the side of the desk while the regular Hair-On-Fire issues arise at work) I realized that I could just repeat the host part of the command, resulting a something like this....

data _null_;
call symputx('SFTPUrl','sftp.tdbank.ca" port=1065 Prot=P HOST="sftp.tdbank.ca');
run;

And after running the process I was ecstatic to see the following contained in the log (some obfuscation to make my security guys happy)

42 Filename AdvInt00 FTP "'FIPP.C.D.FPDAT390.ADVISOR.INTEREST(0)'" User="&TSOUser" Pass="&TSOPass" Host="&SFTPUrl"
43
44 recfm=FB lrecl=90
45 ;
46
47 Data AdvInt00 ;
48 Infile AdvInt00 ;
49 Input
50 @009 RRCode $Ebcdic4.
51 @013 AdvType $Ebcdic3.
52 @016 ACF2 $Ebcdic8.
53 @024 Seqn S370FF3.
54 ;
55 If Seqn eq 0 ;
56 If RRCode eq: 'F' Then Bus='TDW-FP ' ;
57 else Bus='TDW-IA ' ;
58
59 run ;

NOTE: 220-TCPSFTP1 IBM FTP CS V2R3 at **Location**, 08:51:20 on 2022-03-16.
NOTE: User TEEDL has connected to FTP server on Host **Location** .
NOTE: The infile ADVINT00 is:
Filename='FIPP.C.D.FPDAT390.ADVISOR.INTEREST(0)',
NOTE: 15740 records were read from the infile ADVINT00.
NOTE: The data set WORK.ADVINT00 has 11902 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 4.94 seconds
cpu time 0.21 seconds

Bottom line, it is looking like I have the desired one line update solution for the multitude of downstream routines.

Yes, this is not a long term solution but this process is not a long term one and will most likely be sent out to pasture in the next year or two. So this was just a simple thing to get by the existing routine.

And a quick note on the IBM Z/OS environment. While it is at some locations being replaced with 'fancier' platforms, in other instances it is soldiering on efffectively, performing the myriad of operations it was designed for and that the blazingly fast server farms we currently see continue to fail to achieve. Further, in some cases the many, many, many years of upgrading and expanding have made most applications running on our environment fairly complex, and to move off that platform would require a MAJOR (all caps intended) allocation of resources and carry within it some risk. And the offsetting benefits would not be enough to justify the cost. 

[Kurt_Bremser]

It seems that I did express myself incorrectly. I did not mean that you will abandon the mainframe, but that there will be software running on other platforms from which you will have to pull data to your data warehouse. Preparing for that will be a wise move.

[LionelT]

Hi Kurt.

Correct, we are already in that boat where we have multiple systems in different environments that are all being used to support the business needs of the entity. The Host piece is really the 'core' element, with the outlying systems adding a little bit more functionality or chrome at times, and all requiring that some or all of those components to be integrated into one big 'picture'.

This is where I have always believed that SAS excels as it is a multiplatform software tool that can 'stretch' across operating systems and consolidate the information, or Data so to speak back into one big 'pile' for further dissemination. And for those true 'veterans' on the list, a picture of my SDSF screen! Just to show that what is 'old' is also 'new'!

[Tom]

Great idea.

Plus now the macro variable no longer has unbalanced quotes.

So you could just use a %let to set it.  And if any of the programs try to print it using %PUT you won't totally blow up your SAS session.

%let SFTPUrl=sftp.tdbank.ca" port=1065 Prot=P HOST="sftp.tdbank.ca;

[Quentin]

I like it.  Suggest you accept your final answer as the solution.  Of course in your real code, you'll want to comment the heck out of this little hack, to remind your future self of why you created this oddity.

[Kurt_Bremser]

Trying to do everything with one macro variable would mean that you have to put the quotes around the hostname into the macro variable and remove them from the macro variable call, necessating an edit of all codes anyway.

I would suggest to use a scriptable edit tool like sed or awk to simply replace

"&TDMFURL"

with

"&TDMFURL" port=1065 Prot=P

in all programs.

We solved a similar issue by using a macro that defines the complete filename statement; when we switched to SFTP, we had to change only this macro, as the codes themselves use only the file reference after the macro call.

Since the "internal" SFTP of SAS (which in fact uses an external sftp program, depending on OS) has proven to be unreliable with larger file sizes and less than super-performant networks (it starts to read while the download is still in progress, and then runs into synchronization issues when SAS tries to read past the current end of the download), we actually call the external sftp to download the file first to the WORK directory, and establish the file reference as a FILENAME to that local file.

[LionelT]

Thanks Kurt.

I suspect I will have no choice but to edit the filename statements in the routines as you noted.

I had seen the sftp file down in batch to a work directory and then read in solution, however our challenge was that the process did not like some of the more esoteric characters contained in a binary EBCDIC file and as a result the read on the ASCII based machine did not always work out as anticipated. 

[Kurt_Bremser]

As you are now retrieving data from the mainframe, I can almost guarantee you that there will be a change of platform in the future (the company I was with before my retirement has its database now on a mainframe emulation on Linux). So I can only second what others said: create a file transfer macro that either specifies the (S)FTP filename directly or copies the data and provides a local file reference.

You will have to go through all your codes once, but from then on you have a single point where changes need to be made.

"Our" macro uses parameters hostname, operating system of host, username, filename, record length and file type (fixed or variable).