SAS: Create an empty file

There are two ways to create an empty SAS dataset having the same fields and formats as an existing data set. I knew about the DATA step way (shown first) but did not know about the PROC SQL way (shown second):


data work.orig;
a = 1; b = 2; c = 3; output;
a = 2; b = 3; c = 4; output;
format b 3.1 c 4.2; * to show format is copied also ;
run;

data work.copy1;
set work.orig;
delete;
stop;
run;

proc contents data=work.copy1;
run;

proc sql noprint;
create table work.copy2 like work.orig;
quit;
run;

proc contents data=work.copy2;
run;

SAS: PROC PWENCODE

Something that has always bothered me is that the passwords for our databases are readable to anyone who can see our SAS code.

I define all database user IDs and passwords as macro variables in a constants file which is %included in my programs.  They are used in the libname statement as follows:

libname XXXLIB odbc datasrc=oracle user=&GLBL_ORACLE_USERID password="&GLBL_ORACLE_PASSWORD" schema=XXX;

A coworker was nice enough to point me to PROC PWENCODE, which I was not previously aware of.

This proc encodes a password. The encoded password appears in the log, and is stored as macro variable _PWENCODE. 

I decided to try it with a UP file to see if it worked.  Here is my code:

* encode the real password... ;

proc pwencode in="&GLBL_ORACLE_PASSWORD";

run;

%put The encoded password is &_PWENCODE;

%let GLBL_ORACLE_PASSWORD = &_PWENCODE;

libname XXXLIB odbc datasrc=oracle user=&GLBL_ORACLE_USERID password="&GLBL_ORACLE_PASSWORD" schema=XXX;

The encoded password looks like this – the {SAS002} prefix is recognized by the SAS access engine and decoded at that time:

The encoded password is {SAS002}8083755C07C000D90BB19F7A20A761E9

So I could encode the password in a one-time program, then store that password (with the {SAS002} prefix) as GLBL_ORACLE_PASSWORD in my constants file.

Of course, it would still be plain text in the constants file, but at least it would be useable only thru SAS, and we would not be exposing the “real” password to the whole world.