DATA Step, Macro, Functions and more

create macro for password when connecting to db2

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

create macro for password when connecting to db2

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!


Accepted Solutions
Solution
‎12-19-2016 01:24 PM
PROC Star
Posts: 1,760

Re: create macro for password when connecting to db2

Posted in reply to clear6690

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

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

View solution in original post


All Replies
PROC Star
Posts: 1,760

Re: create macro for password when connecting to db2

Posted in reply to clear6690

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

New Contributor
Posts: 4

Re: create macro for password when connecting to db2

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

PROC Star
Posts: 1,760

Re: create macro for password when connecting to db2

[ Edited ]
Posted in reply to clear6690

try searching for :  sas %window

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

New Contributor
Posts: 4

Re: create macro for password when connecting to db2

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?

Solution
‎12-19-2016 01:24 PM
PROC Star
Posts: 1,760

Re: create macro for password when connecting to db2

Posted in reply to clear6690

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 183 views
  • 0 likes
  • 2 in conversation