PWENCODE Procedure

Example 2: Using an Encoded Password in a SAS Program

Features:

IN= argument

OUT= option

Details

This example illustrates the following:
  • encoding a password and saving it to an external file
  • reading the encoded password with a DATA step, storing it in a macro variable, and using it in a SAS/ACCESS LIBNAME statement

Program 1: Encoding the Password

filename pwfile
'external-filename';
proc pwencode in='mypass1' out=pwfile;
run;

Program Description

Declare a fileref.
filename pwfile
'external-filename';
Encode the password and write it to the external file. The OUT= option specifies which external fileref the encoded password is written to.
proc pwencode in='mypass1' out=pwfile;
run;

Program 2: Using the Encoded Password

filename pwfile
'external-filename';
options symbolgen;
data _null_;
infile pwfile truncover;
input line :$50.;
call symputx('dbpass',line);
run;

libname x odbc dsn=SQLServer user=testuser password="&dbpass";

Program Description

Declare a fileref for the encoded-password file.
filename pwfile
'external-filename';
Set the SYMBOLGEN SAS system option. This step shows that the actual password cannot be revealed, even when the macro variable that contains the encoded password is resolved in the SAS log. This step is not required in order for the program to work properly.
options symbolgen;
Read the file and store the encoded password in a macro variable. The DATA step stores the encoded password in the macro variable DBPASS.
data _null_;
infile pwfile truncover;
input line :$50.;
call symputx('dbpass',line);
run;
Use the encoded password to access a DBMS. You must use double quotation marks (“ ”) so that the macro variable resolves properly.
libname x odbc dsn=SQLServer user=testuser password="&dbpass";

Log

1    filename pwfile 'external-filename';
2    options symbolgen;
3    data _null_;
4    infile pwfile truncover;
5    input line :$50.;
6    call symputx('dbpass',line);
7    run;

NOTE: The infile PWFILE is:
      Filename=external-filename
      RECFM=V,LRECL=256,File Size (bytes)=4,
      Last Modified=12Apr2012:13:23:49,
      Create Time=12Apr2012:13:23:39

NOTE: 1 record was read from the infile PWFILE.
      The minimum record length was 4.
      The maximum record length was 4.
NOTE: DATA statement used (Total process time):
      real time           0.57 seconds
      cpu time            0.04 seconds
8
9   libname x odbc
SYMBOLGEN:  Macro variable DBPASS resolves to {sas002}bXlwYXNzMQ==
9 !                dsn=SQLServer user=testuser password="&dbpass";
NOTE: Libref X was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: SQLServer
Copyright © SAS Institute Inc. All Rights Reserved.