How do I use a macro variable for my password in a libname statement as in the example below:
%let mypwd = "{SAS002}9A8C3F4E20629B7F34D2C88A5569E5995AE23782";
libname bigdata odbc noprompt="Driver={Amazon Redshift (x86)};
Server=server.domain.edu;
Database=prod; UID=mylogin; PWD=&mypwd; Port=5439" schema=nyu;
run
I believe the fact that the connection info is already inside double quotes is causing the issue. I've confirmed that I have the connection info correct and that it works if I enter the password directly without encoding it and using a variable.
I was not able to get SAS to recognize encrypted passwords when using NOPROMPT to connect Redshift via ODBC.
Check with your admins and see if they can get the Server, Port and Database mapped to a DNS name so that you can just use the DNS= option instead of NOPROMPT. Then you should be able to get it to recognize the encrypted password.
So if they defined rs_server as the DSN name then your code might look like this.
%let username=rs_user ;
%let password=raw_password ;
%let epassword={SASENC}6FE4B62604378F370E21797F12E892A9 ;
libname test odbc dsn=rs_server schema=my_schema
user="&username" password="&epassword"
;
Try adding some more quotes.
libname bigdata odbc noprompt="Driver={Amazon Redshift (x86)};
Server=server.domain.edu;
Database=prod; UID=mylogin; PWD="&mypwd"; Port=5439" schema=nyu;
run
I did try adding quotes around the macro variable (e.g.
PWD="&mypwd";
But SAS returns a "FATAL: password authentication failed for user" error.
ERROR: Error in the LIBNAME statement.
Your target won't understand the SAS password encoding. See http://support.sas.com/kb/31/602.html
It only seems to work with the basic password= option.
I was not able to get SAS to recognize encrypted passwords when using NOPROMPT to connect Redshift via ODBC.
Check with your admins and see if they can get the Server, Port and Database mapped to a DNS name so that you can just use the DNS= option instead of NOPROMPT. Then you should be able to get it to recognize the encrypted password.
So if they defined rs_server as the DSN name then your code might look like this.
%let username=rs_user ;
%let password=raw_password ;
%let epassword={SASENC}6FE4B62604378F370E21797F12E892A9 ;
libname test odbc dsn=rs_server schema=my_schema
user="&username" password="&epassword"
;
Try removing the quotes as in this example:
%let mypassword = "passw";
libname redshift odbc datasrc=Redshift user=jdoe password=&mypassword. schema=nyu;
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.