Date:         Fri, 16 Jul 2010 08:35:51 -0400
Reply-To:     Michael Raithel <michaelraithel@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Michael Raithel <michaelraithel@WESTAT.COM>
Subject:      Re: Update a dataset in use by other people
In-Reply-To:  <201007160941.o6G4gMQ5008022@willow.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Dear SAS-L-ers,

Dee posted the following:

> Hi all,
>
> help with this would be greatly appreciated! :
>
> I run a weekly process, which, amongst other things, updates a dataset
> that can be accessed by multiple users.
> The simple datastep I've used so far to update it wouldn't work if
> someone
> somewhere had the dataset open, which would cause the whole process to
> fall over.
> I've created a temporary dataset that has all the right updates/changes
> and all I'm trying to do now is replace the original with the temporary
> one.
> I've tried both proc sql and proc append with the uniquesave option,
> unfortunately
> -some fields in the data contain duplicate values that cannot be
> changed/deleted
> -none of the variables are keys that can be used so that the append
> actually appends instead of updating
>
> I'm not too sure what to do from here - any suggestions?
>
> Thanks in advance for your help!
>
Dee,

Hmmm... when you wrote "... all I'm trying to do now is replace the original with the temporary one" this looked pretty straight-forward, and I thought I had a bead on it.  But your further elucidations concerning duplicate values and keys threw me.  If all you want to do is to simply replace the original file with the temporary one, you could do something along these lines:

filename DATAFIL "G:\dees\proddir\deeprod.sas7bdat";

data _null_;

inuse = 0;

do until inuse;

inuse = fopen('DATAFIL');

end;
run;

proc datasets library=deeprod noprint;
        age deetemp deeprod;
run;
quit;

The code, above, allocates your SAS data set via a FILENAME statement--wait, wait, hear me out before commenting on that!  Then, the DATA NULL step runs and runs and runs and runs until somebody, anybody, everybody DOES NOT HAVE that data set allocated.  At which time, the loop ends, and we drop down onto the DATASETS PROCEDURE.  That procedure uses the AGE statement to clobber the DEEPROD SAS data set by deleting it and renaming DEETEMP to DEEPROD, thereby fulfilling your original statement of "... all I'm trying to do now is replace the original with the temporary one".

If you want something fancier or more elegant, simply stick around the SAS-L coffee house; there are bound to be other helpful posts.  Oh, is that espresso that you are drinking?!?!?!

Dee, best of luck in all your SAS endeavors!


I hope that this suggestion proves helpful now, and in the future!

Of course, all of these opinions and insights are my own, and do not reflect those of my organization or my associates. All SAS code and/or methodologies specified in this posting are for illustrative purposes only and no warranty is stated or implied as to their accuracy or applicability. People deciding to use information in this posting do so at their own risk.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel
"The man who wrote the book on performance"
E-mail: MichaelRaithel@westat.com

Author: Tuning SAS Applications in the MVS Environment

Author: Tuning SAS Applications in the OS/390 and z/OS Environments, Second Edition
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172

Author: The Complete Guide to SAS Indexes
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Deja Brew:  The feeling that you've had this coffee before. - Anonymous
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++