BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
clear6690
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

5 REPLIES 5
ChrisNZ
Tourmaline | Level 20

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

clear6690
Calcite | Level 5

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

clear6690
Calcite | Level 5

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?

ChrisNZ
Tourmaline | Level 20

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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