Macro variable in sas stored process

Question

I have a code in SAS.

proc sql;
create test as select * from test2 where user_name = &username;quit;

when I Run in SAS EG with %let username=Bob it runs normal. But I need to run it with stored Process (variable username is internal parameter in connected user and I delete %let username=Bob from sas stored process code). Stored Process brake with an error.

In log file I see variable:

_username=Bob but code in stored process can not find it. How to use this variable in SAS Stored process code? Thank you!

Answer 1

In your own SAS code, you use a macro variable username, but in your stored process, the user name is available in the reserved macro variable _username. The underscore is really part of the variable name, so you should write &_username.

For more information, you can read the documentation or you can consult the log after inserting

%put _automatic_;

in your code, to print all macro variables SAS provided for you.

Remark: As the automatic macro variables in a stored process differ from that in a local SAS session, if you want to use the same code in both, you often need some %if %then %else logic.