topic Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again? in SAS Programming

How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

mcrum1 — Wed, 06 Aug 2014 16:45:58 GMT

Hello! I’m still pretty new to SAS. I was wondering how would I change the libname statement to “read only” after it creates the tables in a program and prevent it from overwriting existing tables if I run the program again? Also, if the program gets ran again, I would like to prevent it from overwriting existing tables.

I tried running the libname without “ACCESS=READONLY” in the beginning of the program and including the libname again with “ACCESS=READONLY” at the end of the program. It doesn’t prevent my tables from getting overwritten if the program gets ran again.

LIBNAME X "&UDIR\SAS TABLES\&MP1DB_YYMMN6";

My Tables in Program

LIBNAME X "&UDIR\SAS TABLES\&MP1DB_YYMMN6" ACCESS=READONLY;

Thanks so much!

Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

PGStats — Wed, 06 Aug 2014 17:08:55 GMT

Try using dataset option (REPLACE=NO) in the step that may overwrite the table. - PG

Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

jwillis — Wed, 06 Aug 2014 17:18:56 GMT

I used two macro variables. When I call the program, and it is the first running, I make my library name the 'update' library. When I am re-running the code, I make my library the read only library.

(I did not check this for syntax and misspellings)

%let rerun=no;

libname writeover 'my\writable\table\folder\location';

libname readonly 'my\readonly\table\folder\location';

%macro rerun;

%if &rerun=no %then %do;

%let libnm=writeover;

%end;

%else %if rerun=yes %then %do;

%libnm=readonly;

%end;

%else %do;

%let libnm=work;

%end;

%mend;

%rerun;

data &libnm..dataout;

set writable.datain;

run;

Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

Astounding — Wed, 06 Aug 2014 17:22:48 GMT

Just a guess here ... that sometimes the reason this question comes up relates to having blown away a data set by leaving out a semicolon on the DATA statement. This program, for example, replaces a good data set with garbage:

data perm.old

set perm.old;

total = 2 * pints;

run;

To prevent this from happening, the DATASTMTCHK option can restrict what is a valid data set name. For example, it can prevent the above DATA step from running by classifying SET as an invalid data set name. The possible values include COREKEYWORDS (such as SET, MERGE, UPDATE), ALLKEYWORDS (adds DATA step statements as invalid data set names) and NONE (no restrictions on data set names).

Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

SASBob — Wed, 06 Aug 2014 18:14:09 GMT

if you are running the program a second time then yes, it will overwrite the dataset.

what you have supplied as your code is similar to below.

libname x "d:\temp";

data x.one;

today=today(); /* this will create the dataset */

run;

libname x clear;

libname x "d:\temp" access=readonly;

data x.one;

today=today()-1;

format today date9.; this will NOT overwrite the dataset due to the ACCESS=readonly */

run;

If you rerun the code the first one will again, overwrite your dataset.

Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

Tom — Wed, 06 Aug 2014 19:09:02 GMT

If you are going to re-run the program then the program itself needs to be smart enough not to overwrite (or whatever logic you want).

In general I have found it is useful to separate derived data programs (or ETL) from analysis programs.

One way to help eliminate confusion is to force the program that wants to write to use a different LIBREF.

So you might have a standard

LIBNAME MYDATA '.....' ACCESS=READONLY;

at the top of all of the programs. And then the programs that need to create new data in the library might create a new libref.

LIBNAME WMYDATA '....' ;

data wmydata.new ;

...

run;

libname WMYDATA clear ;

Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

SASBob — Wed, 06 Aug 2014 19:18:59 GMT

Tom,

after I looking at my response, I don't think I could have been any more vague. thank you for putting into words what I was trying to say

Bob

Re: How would I change a libname statement to “read only” and prevent tables from getting overwritten if I run the program again?

Ksharp — Thu, 07 Aug 2014 13:17:46 GMT

1) make a view for each and everyone of tables.

2) add dataset's option alter= to avoid being overwrite problem .

Xia Keshan