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!
You could check whether the variables exist and have a length before calling %window.
See the %length() and %symexist() functions.
Can you prompt the user once, using a %window for example, and then use the credentials for both the signon and the db2 connection?
Do you mind sharing the code if possible? I tried to google %window but it does not seem related. Thanks!
try searching for : sas %window
you'll find many links, such as http://www2.sas.com/proceedings/sugi27/p192-27.pdf
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?
You could check whether the variables exist and have a length before calling %window.
See the %length() and %symexist() functions.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.