topic Re: Clear oracle library at end of the sas session in Administration and Deployment

Clear oracle library at end of the sas session

dcmacedo — Thu, 30 Nov 2017 15:37:09 GMT

I created a oracle library to use as "work" in sas guide, where the users can create "work" tables directly in oracle. In our cenary the dimensions and facts are in oracle enviroment and this approach otimises the querys. I searching for a way to delete this "oracle work tables" after the end of the sas session.

Re: Clear oracle library at end of the sas session

paulkaefer — Thu, 30 Nov 2017 15:51:29 GMT

You can easily clear all datasets from a library (source😞

proc datasets library=lib kill;
run;
quit;

I'm not sure if there's an equivalent to the autoexec but at the end, rather than beginning, of a SAS session. This could be a good opportunity to educate the users on the difference between work and permanent libraries, but I agree that it would be nice to have this functionality.

I submitted a comment to @ChrisHemedinger on his related blog article. If there's not a way to have an autoexec on session close, I'd say we should submit it as a SASware Ballot Idea.

Re: Clear oracle library at end of the sas session

ChrisHemedinger — Thu, 30 Nov 2017 16:01:57 GMT

Paul, there isn't a hook for individual users to set. But an admin can set a Termination Program in the Workspace definition. See this topic in the admin guide.

Re: Clear oracle library at end of the sas session

dcmacedo — Thu, 30 Nov 2017 20:40:48 GMT

Thanks for the answer. @paulkaefer, this is exactly the code I'm going to use. In the future I intend to switch to an Oracle stored process.

Re: Clear oracle library at end of the sas session

dcmacedo — Thu, 30 Nov 2017 20:41:47 GMT

Thanks for the reference @ChrisHemedinger, it's exactly this feature I was looking for, I'm new to SAS administration, so I was unaware of this configuration.

Re: Clear oracle library at end of the sas session

ChrisHemedinger — Thu, 30 Nov 2017 20:48:27 GMT

My answer assumes that you're running this in a SAS workspace via EG or SAS Studio. If running with interactive SAS on Windows, there is the TERMSTMT option. This does not work on Workspace/Stored Process servers.

Re: Clear oracle library at end of the sas session

dcmacedo — Thu, 30 Nov 2017 21:22:57 GMT

We have SAS Enterprise BI, but we only use SAS Data Integration (sas batch server), SAS Enterprise Guide and SAS Web Report Studio. Our BI is in low demand, our users are mainly using SAS Guide to perform analyzes directly in our Oracle database (90% of our data is in Oracle).
Our users love SAS Guide.