SAS Procedures

Help using Base SAS procedures
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gil_
Quartz | Level 8
I need to encrypt the pw ln my log
I have this now

Options symbolgen;
%include "h:\passwords.sas";
Proc sql;
Connect to Oracle (user=&user password=&ps path=&db);
Create table table as select * from connection to Oracle


I need the log to show xxxxx for passwords
1 ACCEPTED SOLUTION

Accepted Solutions
Sven111
Pyrite | Level 9

@SASKiwi is correct, you basically just run this code:

 

proc pwencode 
    in='mypassword' 
    method=sas005;
run;

Look in the log file for the results, which when I ran it resulted in (let's say) 

'{SAS005}D03BE345341DB5A0A850BD0ABBD3BB49E8711BFF91749877FE1'

Then you'd copy that full text above into your connect string as @SASKiwi showed:

Connect to Oracle (user=MyUserName
PASS='{SAS005}D03BE345341DB5A0A850BD0ABBD3BB49E8711BFF91749877FE1' 
PATH=MY_ORACLE_DB_NAME
SCHEMA=MySchema)

You can also use that password in a LIBNAME connection to Oracle, which I find much more convenient since you can then use it for both implicit and explicit SQL passthrough statements.

LIBNAME OracSAS ORACLE USER=MyUserName  
PASS='{SAS005}D03BE345341DB5A0A850BD0ABBD3BB49E8711BFF91749877FE1'
PATH= My_Oracle_DB_Name
SCHEMA=MySchema;

PROC SQL;
    /* Implicit SQL-Passthrough */
    SELECT *
    FROM   OracSAS.TableName;

    /* Explicit SQL-Passthrough */
    CONNECT USING OracSAS AS OracDB;
    SELECT *
    FROM CONNECTION TO OracDB (SELECT * FROM TableName);
    DISCONNECT FROM OracDB;
;QUIT;

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

Turn OFF symbolgen for one thing.

It you are running the CONNECT statement inside a macro then also turn off MPRINT (and probably also the older obscure MACROGEN option).

Sven111
Pyrite | Level 9

I've found PROC PWENCODE is the most bullet-proof method, although it's largely security by obscurity.  If all you need is to prevent the plain-text password from being available to people snooping around your files it works well enough though. Especially if you use that password for other stuff (which is also far from ideal, but a different conversation). 

 

Definitely use method sas004 or sas005 if available on your SAS version though.

proc pwencode 
    in='mypassword' 
    method=sas005;
run;
Gil_
Quartz | Level 8
Hi Sven
I want to use your example this to connect Proc sql;
Connect to Oracle (user=&user password=&ps path=&db);
Create table table as select * from connection to Oracl
How do I refer
proc pwencode in='mypassword' method=sas005; run
; to connect?
SASKiwi
PROC Star

PWENCODE will write the encoded password to the SAS log. You then copy the password string, including the sas005 on the front into your CONNECT statement: Connect to Oracle (user=&user password='sas005MyEncodedPassword' path=&db).

Sven111
Pyrite | Level 9

@SASKiwi is correct, you basically just run this code:

 

proc pwencode 
    in='mypassword' 
    method=sas005;
run;

Look in the log file for the results, which when I ran it resulted in (let's say) 

'{SAS005}D03BE345341DB5A0A850BD0ABBD3BB49E8711BFF91749877FE1'

Then you'd copy that full text above into your connect string as @SASKiwi showed:

Connect to Oracle (user=MyUserName
PASS='{SAS005}D03BE345341DB5A0A850BD0ABBD3BB49E8711BFF91749877FE1' 
PATH=MY_ORACLE_DB_NAME
SCHEMA=MySchema)

You can also use that password in a LIBNAME connection to Oracle, which I find much more convenient since you can then use it for both implicit and explicit SQL passthrough statements.

LIBNAME OracSAS ORACLE USER=MyUserName  
PASS='{SAS005}D03BE345341DB5A0A850BD0ABBD3BB49E8711BFF91749877FE1'
PATH= My_Oracle_DB_Name
SCHEMA=MySchema;

PROC SQL;
    /* Implicit SQL-Passthrough */
    SELECT *
    FROM   OracSAS.TableName;

    /* Explicit SQL-Passthrough */
    CONNECT USING OracSAS AS OracDB;
    SELECT *
    FROM CONNECTION TO OracDB (SELECT * FROM TableName);
    DISCONNECT FROM OracDB;
;QUIT;

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 5528 views
  • 3 likes
  • 5 in conversation