When you assign a value to a macro variable that does not already exist then one is created. If you do that inside a macro then it is created as LOCAL to the macro. So once the macro finishes running it is gone.
So you have two solutions.
1) Make sure the macro variable already exists before calling the macro.
%macro test_mcr1 (user, secrete);
%let conn_creds=user="&user" password="&secrete";
%mend;
%let conn_creds=before macro call;
%test_mcr1(user=FRED,secrete=MYPASS);
%put &=conn_creds;
2) Make the macro smart enough to make a GLOBAL macro variable if one does not already exist. If you use this method remember to test if there is already a macro variable before trying to make a GLOBAL macro variable. That is because if there already a LOCAL macro variable (perhaps in some other currently running macro that called this one) then trying to make a GLOBAL macro variable will generate an error.
%macro test_mcr1 (user, secrete);
%if not %symexist(conn_creds) %then %global conn_creds;
%let conn_creds=user="&user" password="&secrete";
%mend;
%test_mcr1(user=FRED,secrete=MYPASS);
%put &=conn_creds;
But perhaps for your use case you don't really need a macro variable at all. Instead you could make a macro you can call in the middle of the LIBNAME (or PROC SQL CONNECT statement).
%macro conn_creds;
user="FRED" password="MYPASS"
%mend conn_creds;
libname libref odbc %conn_creds dsn=xyz;
Make sure the macro does not emit any semicolons.
... View more
