my SAS runs on my organisation server. When i want to extract data in the organisation location in an Excel file i use the below :
proc export dbms=excelcs data=&&var&i.
outfile="&outpath.\&&var&i.._&today..xls";
sheet=&&var&i.;
version="2002";
server = "organisationservername";
port = 8000;
ServerUser = "compuk\&lwin";
ServerPass = "&pwin";
RUN;
the problem with the above code is that i have to provide my ServerPass which is my personal windows password. Since the code used by multiple users if i save it in the program file it will be visible to everyone. I saved the below in the Tools > options > SAS programs > Submit SAS code when server is connected
/* Insert custom code after server connection here */ options symbolgen; proc pwencode in='my personal password goes here' method=sas002; run; %let user_pass=&_pwencode.; *--------------------- End of User password setup ----------------; proc sql noprint; select value into: user_id from sashelp.vmacro where name="_CLIENTUSERID"; quit; %let user_id=%sysfunc(dequote(&user_id.)); %let lwin=&user_id.; %let pwin=&user_pass.;
but when i run Proc Export i receive the below error :
ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER='<domain\username>'; SERVERPASS='<password>'; enclosed in quotes).
i want to find a way to save somewhere my password and when i want to extract to Excel file SAS does not need to ask me to provide it
The issue of sharing code where a private password is needed is solved like this:
Every user has a program file which sets the macro variables containing the credentials; the file is stored in the same location in each user's HOME, and the permissions are set so that only the user can read it.
The shared programs will then contain this
%include '$HOME/sas/password.sas`;
to fetch the credentials.
This is because the password encrypted by SAS is different from the password stored at the server level.
The solution depends upon the environment architecture. Speak to the administrators.
The issue of sharing code where a private password is needed is solved like this:
Every user has a program file which sets the macro variables containing the credentials; the file is stored in the same location in each user's HOME, and the permissions are set so that only the user can read it.
The shared programs will then contain this
%include '$HOME/sas/password.sas`;
to fetch the credentials.
@Kurt_Bremser wrote:
The issue of sharing code where a private password is needed is solved like this:
Every user has a program file which sets the macro variables containing the credentials; the file is stored in the same location in each user's HOME, and the permissions are set so that only the user can read it.
The shared programs will then contain this
%include '$HOME/sas/password.sas`;
to fetch the credentials.
Or better still develop your own macro that reads from the text file so that no macro variable is needed. You could use the MODE=3 method of this macro: https://github.com/sasutils/macros/blob/master/fread.sas
Place the file with the password into a folder like .ssh that only you can read.
ServerPass = "%read_password($HOME/.ssh/organisationservername.txt)"
Is there a particular reason why you are creating really old 2002 format XLS spreadsheets? If you changed to using XLSX spreadsheets, SAS can produce these itself and then you wouldn't need to use the SAS PC Files Server and to supply user names and passwords.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.