topic Re: Reading data from mainframe via ftp in Foundation SAS in SAS Programming

Reading data from mainframe via ftp in Foundation SAS

mediaeval — Tue, 24 Sep 2013 15:12:18 GMT

I’ve written a Foundation SAS program which retrieves data repeatedly (34 times in total) from an IBM Mainframe via ftp. In order to do this, I use a Filename statement like the following:

%Let Username = ‘JohnB’;

%Let Password = ‘passw’;

Filename FileRef ftp &MainframeFile Host=&Host User=&Username Pass=&Password Debug lrecl=&RecLength recfm=f;

While testing, I hard-coded the password in the program to save time. Since the password then appears in the Log, I’ve removed the password and changed the Filename statements in the program as follows:

%Let Username = ‘JohnB’;

Filename FileRef ftp &MainframeFile Host=&Host User=&Username Prompt Debug lrecl=&RecLength recfm=f;

This new approach means that the user has to type in his/her password manually, and it will be printed to the Log as a string of X’s.

Unfortunately, this approach means that the user will be asked for his/her password 34 times when the program is run. Is there a way in SAS to only pass the password through to the mainframe via ftp just once, even though multiple ftp statements are used? Or is it possible to not have the password written to the Log? Is there an option in SAS that doesn't write certain statements to the Log? This would mean taking the statement %Let Password = ‘passw’ and not writing that to the Log.

Re: Reading data from mainframe via ftp in Foundation SAS

CTorres — Tue, 24 Sep 2013 16:00:48 GMT

You can consider using PROC PWENCODE. See Base SAS(R) 9.3 Procedures Guide, Second Edition

Regards,

Re: Reading data from mainframe via ftp in Foundation SAS

jakarman — Tue, 24 Sep 2013 18:06:25 GMT

The real issue of pwencode is that the source code you can still copy/paste that and it will work.

By that everyone is able to used that once having copied it.

Using pwencode will solve just the incidental seeing as dificult to remember.

It will be reversed by SAS to its original value before sending out. As SAS is able to do that someone else could do that also.

What can be done is a dynamic part in the SAS source solving it at the moment when needed.

Where must your key-password come from? My suggestion would be a password encrypted file belonging to the data/key running the needed process.

Sounds posssible complex but can be solved with a SAS macro. Need a sample? Watch the read-key value needing to be the same.

Sample to create SAS dataset

Data lib.keypswd (read=P1jkU7r write=secret-me alter=secret-me) ;
   length id $ 16 keypsw $ 32 ;
   id="orion" ; keypsw="test app" ; output;
   id="PMIB" ; keypsw='user="P_RSI_CI" pw="Z75AftgTb"' ;output;
run;

Sample SAS macro:

/* * * xkeypswd * * * */
/* * * read personal key-pswd to acess external dbms * * * */
/* * * written by:jakarman * * * */
/* * * designed 2007 (8.2) converted 2012 (9.-) * * * */

%macro xkeypsw (mainarg,opt,readvl=P1jkU7r,keypswfl=lib.keypswd) ;
%local larg lind xkeypswdret ;
%let opt =%lowcase(&opt) ;
%let larg=%length(&mainarg) ;
%let lopt=%length(&opt) ;

%let xkeypswdret=;
%if ( %index(&opt,HELP) >0 | %index(&opt,?) >0 | %index(&mainarg,?) >0 ) %THEN %do ;
%* (insert your comment as copied this sample source) ; %let xkeypswdret=help
text given ;
%end ;

%if ( %length(&xkeypswdret) = 0) %then %do ;

   %let keydsid=%sysfunc(open(&keypswfl ( read=&readvl where=( id = "&mainarg") ) ,i ));
   %if (&keydsid = 0) %then %put %sysfunc(sysmsg());
   %else %do ;
      %let rc=%sysfunc(fetch(&keydsid ));
      %if ( &rc ne 0) %then %put %sysfunc(sysmsg()) ;
      %let pw=%sysfunc(getvarc(&keydsid,%sysfunc(varnum(&keydsid,keypsw)))) ;
&pw
   %end ;
   %if ( &keydsid > 0 ) %then %let rc=%sysfunc(close(&keydsid)) ;
%end;

%mend ;