topic Re: create macro for password when connecting to db2 in SAS Programming

create macro for password when connecting to db2

clear6690 — Wed, 14 Dec 2016 20:33:42 GMT

Hi,

For each SAS job, we start with running a login.sas file to connect to the server. A window will pop up for us to enter the user name and password. Please see the script below:

%macro login();
%let mynode=iw603e 6048;
options comamid=tcp remote=mynode;
signon user= _PROMPT_ password=_PROMPT_ ;
%let rm_server=mynode;
libname rwork slibref=work server=&rm_server;

%mend;

%login;

Recently, there is a change for the SAS db2 connection script so that we need to update the user name and password everytime we run SAS:

proc sql;
connect to db2 (user=user123 password=pass123 database=AAAAAAAA);
create table table_abc (compress=yes) as select* from connection to db2 (
select *
from ABC.table_abc
);
quit;

Is it possible that we can extract the same user name and password from what we entered when running the login.sas file to update in the second piece of code? I can use &sysuserid. for the user name but how about the password?

Very appreciated!

Re: create macro for password when connecting to db2

ChrisNZ — Wed, 14 Dec 2016 21:29:26 GMT

Can you prompt the user once, using a %window for example, and then use the credentials for both the signon and the db2 connection?

Re: create macro for password when connecting to db2

clear6690 — Wed, 14 Dec 2016 21:51:31 GMT

Do you mind sharing the code if possible? I tried to google %window but it does not seem related. Thanks!

Re: create macro for password when connecting to db2

ChrisNZ — Wed, 14 Dec 2016 22:32:58 GMT

try searching for : sas %window

you'll find many links, such as http://www2.sas.com/proceedings/sugi27/p192-27.pdf

Re: create macro for password when connecting to db2

clear6690 — Thu, 15 Dec 2016 17:02:17 GMT

Thank you ChrisNZ. I tried the &window statement and now I can create macro for both of user ID and Passoword.

The only issue occurs we run monthly batch every month, which contains multiple SAS jobs. In the old way, the system signon only requires one-time input of user name and password and the window won't pop up for the next SAS job because we already signed in. However, with the &window statement, the window will pop up everytime when the next SAS job is running, and it won't continue if no one presses ENTER. Is there a way to solve this issue?

Re: create macro for password when connecting to db2

ChrisNZ — Thu, 15 Dec 2016 21:17:15 GMT

You could check whether the variables exist and have a length before calling %window.

See the %length() and %symexist() functions.