topic Re: Proc work library Dataset kill option : Error :Work dataset does not exists in SAS Programming

Proc work library Dataset kill option : Error :Work dataset does not exists

JJP1 — Thu, 09 May 2019 06:11:59 GMT

Hi ,

I am using below user written code to kill the datasets in work library in SAS DI job librray after job's logic is completed .

proc datasets library=work kill;
run;
quit;

There are so many work tables are created as part of this SAS DI job ,But after running job we are getting error in above mentioned user written node that only work.table1 and work.table2 does not exist.

please suggest how can we resolve the error

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

ScottBass — Thu, 09 May 2019 06:24:40 GMT

Try:

proc datasets library=work kill nowarn nolist;
run;
quit;

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

JJP1 — Thu, 09 May 2019 08:26:24 GMT

Hi ,

I ve tried below option and i am getting same error as work.table does not exist error.please help.

Thanks

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

ShiroAmada — Thu, 09 May 2019 09:11:55 GMT

Try sharing or posting an excerpt of the log where the ERROR first occurred.

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

JJP1 — Thu, 09 May 2019 10:19:41 GMT

Hi,

Actually we cretead parametrised SAS DI job, in it we used userwritten node code to kill the work datasets.

and this parameterised job is being used in loop job and running the loop job based on year by year

,so when we run loop job we are getting error as below

ERROR: File WORK.WWWWWW.DATA does not exist

ERROR: File WORK.WWWWWW.DATA does not exist.

loop job work fine with out user written node(where we are killing work tables ) please help.but we do not want work tables to be stored

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

ballardw — Thu, 09 May 2019 14:31:45 GMT

@JJP1 wrote:

Hi,

Actually we cretead parametrised SAS DI job, in it we used userwritten node code to kill the work datasets.

and this parameterised job is being used in loop job and running the loop job based on year by year

,so when we run loop job we are getting error as below

ERROR: File WORK.WWWWWW.DATA does not exist

ERROR: File WORK.WWWWWW.DATA does not exist.

loop job work fine with out user written node(where we are killing work tables ) please help.but we do not want work tables to be stored

Looks like more of the actual code with the error message. Copy both the code and the error.

Something is explicitly naming a data set for some use and possibly your Proc DATASETS deleted it before this other code tries to use the data set.

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

ScottBass — Thu, 09 May 2019 23:25:17 GMT

@JJP1 wrote:

Hi,

Actually we cretead parametrised SAS DI job, in it we used userwritten node code to kill the work datasets.

and this parameterised job is being used in loop job and running the loop job based on year by year

,so when we run loop job we are getting error as below

ERROR: File WORK.WWWWWW.DATA does not exist

ERROR: File WORK.WWWWWW.DATA does not exist.

loop job work fine with out user written node(where we are killing work tables ) please help.but we do not want work tables to be stored

Provide the code and/or the SAS log where you're getting these errors.

PROC DATASETS LIB=WORK KILL NOWARN NOLIST will not generate this error for non-existent data sets.

(Interesting naming convention for your dataset names 😉 )

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

JJP1 — Mon, 13 May 2019 05:50:07 GMT

Hi,

I am not getting any error now if i use below code in user written node transformation,but i really did not understood why i was getting error in previous code and now not .even though the code looks almost same.could you please help.

options dlcreatedir;
libname mywork "%sysfunc(getoption(WORK))/mywork";

/* optional: one-level names will default to MYWORK lib */
options user=mywork;
/* cleanup MYWORK before the next task */
proc datasets lib=mywork kill;

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

Patrick — Mon, 13 May 2019 06:02:59 GMT

KILL will just delete whatever table there is in WORK. And it will KILL everything at once so you don't need to have this in a loop.

IF you want to delete tables targeted (via a loop) but you can't be 100% sure that the table actually exists: Add NOLIST NOWARN to PROC DATASETS. NOLIST won't create a report (so add this option also if using KILL), NOWARN will suppress any Warning for deletion attempts on tables that don't exist.

AND... may be also add MT=(DATA VIEW) to ensure that you also delete views if they exist. May be that's the cause of your initial problem - the name you're using is actually a view but proc datasets was only configured to include tables.

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

JJP1 — Mon, 13 May 2019 06:12:12 GMT

Hi @Patrick ,

Thanks for explanation.would you please help where can we add the view option in proc datasets code to delete if there is any view please.

Thanks,

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

Patrick — Mon, 13 May 2019 06:38:35 GMT

@JJP1

Here you go

proc sql;
  create view vtest as select * from sashelp.class;
quit;

/* option 1: using DELETE statement */
proc datasets lib=work nolist nowarn mt=(data view);
  delete vtest;
  run;
quit;

/* option 2: using KILL option */
proc datasets lib=work kill nolist nowarn mt=(data view);
quit;

And as a comment:

DIS jobs in a PROD environment are run in batch. SAS will automatically clean out the WORK area once a SAS Session terminates. For this reason I'd only bother to clean-out work if this is an intermediary step in your DIS job AND your tables in WORK are that big that you risk to exceed your WORK space limits.

And as a last idea: I guess you want to delete all SAS DIS internally created tables and views. They all follow a naming pattern with names starting always with W. IF you follow a naming convention to never use W as beginning for any of the WORK tables/views you've got under control then you could use code as below:


proc datasets lib=work nolist nowarn mt=(data view);
  delete w:;
  run;
quit;

Re: Proc work library Dataset kill option : Error :Work dataset does not exists

ScottBass — Mon, 13 May 2019 21:57:54 GMT

@JJP1 : I'm glad you "solved" your problem. However, time and time and time again, several folks asked that you post the actual code causing the error. Yet nowhere in this thread did you do so. A bit of advice: for future posts, if folks ask you to post your actual code causing the error, I strongly suggest you do so. That way you will actually learn something from the process, and folks will be better equipped to help you.