0

I want to invoke a macro variable as password in the libname statement but it seems like not working. the libname statement I wrote is 

%let pswd=ABCD
libname sa2st oracle path='wegtrse' user=myname password='&pswd' defer=no
connection=globalread readbuff=4000 ;

this gives an error 

Error in the libname statement

and in the log

NOTE: Line generated by the macro variable "PSWD".
18   Xpswd
  ----
  22

How can I invoke the macro variable in this libname statement ?

user3658367
  • 641
  • 1
  • 14
  • 30
  • I'm closing this as dup, as it is explicitly a duplicate of that question as it stands; the suggestions to use PWENCODE and such are good ones, though. I would suggest a new question asking about best practices for using passwords with a more comprehensive answer (and Chris or whomever can write it as a self-answered if user3658367 doesn't want to ask). – Joe Nov 05 '15 at 15:28
  • use double quotes instead of single quotes when referencing a macro variable ie "&pswd" – Altons Sep 25 '16 at 13:15

2 Answers2

3

Macro triggers are not evaluated inside on single quotes. Use double quote character instead. Also make sure to include ending semi-colon in your assignment of the macro variable.

%let pswd=ABCD;
libname sa2st oracle path='wegtrse' user=myname
   password="&pswd" defer=no
   connection=globalread readbuff=4000 
;
Tom
  • 47,574
  • 2
  • 16
  • 29
  • I tried the way you said it gives an error saying "userID/password not valid" and also a warning "Apparent symbolic reference PWD not resolved." I tried without the macro variable to see if the userID/pswd are correct and it worked. – user3658367 Nov 05 '15 at 12:27
  • @user3658367 You also appear to be missing a semicolon after the `%let` statement – Longfish Nov 05 '15 at 13:42
1

Tying this to your related question (SAS/Connect to Oracle with rsubmit), if you're concerned about storing your password in plain-text, you can use the PWENCODE procedure to encrypt it, and then use that in your libname statement.

/* One off (or when password changes), then take {SAS002} result from log */
proc pwencode="mylongpassword" method=sas002 ; run ;

%LET USR = bilbobaggins ;
%LET PWD = {SAS002}B02D643D06D400915C1E7F831D448FD5266E98F15A282918 ;

libname mylib oracle user="&USR" pass="&PWD" path='wegtrse' ;
Community
  • 1
  • 1
Chris J
  • 7,549
  • 2
  • 25
  • 25
  • PWEncode is a joke. Replacing a plain-text password with the result of pwencode just protects people from knowing the password, but does not prevent them from gaining access to the system. The encoded password can be used in place of the regular password (just like a password) so it gives the false appearance of being secure. The only benefit it provides would be if you use the same password across multiple systems, they'll only be able to use the encoded value to gain access to systems via SAS. See - http://stackoverflow.com/questions/8157160/odbc-password-security-in-sas – Robert Penridge Nov 05 '15 at 16:28