how to delete sas dataset created during certain macro run ?

[abhinayingole]

is there any way to delete all sas dataset created only while executing some macro ?

[ChrisHemedinger]

The best way to do this, if you can, is to direct all of the data sets to a temp libname while the macro runs. If the macro allows you to specify the output location, use this temp library as the destination. Use PROC DATASETS to delete its contents when finished, or wait until the SAS session ends and it will be cleaned up because it's a temporary library in the WORK folder. You can also redirect one-level names from the default of WORK to the temp library by using OPTIONS USER=;

%let outdir=%sysfunc(getoption(work));
/* create a results folder in the WORK area */
options dlcreatedir;
libname RES "&outdir./results" access=temp;

/* optionally redirect one-level names from WORK to RES */
options user=RES;

/* run macro and direct output to RES library */
%myMacro(lib=RES);

options user=WORK;

proc datasets lib=RES kill nodetails nolist; quit;
libname RES CLEAR;

 This will also "hide" the generated data sets from client apps that detect them and show/open them automatically, like SAS Enterprise Guide.

[PaigeMiller]

Hello @ChrisHemedinger 

I have done this a long time ago, but as I recall, once you execute the command

options user=RES;

then any data sets in the WORK library now have to be referred to with two-level names, e.g. WORK.MYDATA; and so you can't just refer to this data set as MYDATA.

Is there a workaround? Or is my memory wrong?

[Tom]

---

@PaigeMiller wrote:

Hello @ChrisHemedinger 

I have done this a long time ago, but as I recall, once you execute the command

options user=RES;

then any data sets in the WORK library now have to be referred to with two-level names, e.g. WORK.MYDATA; and so you can't just refer to this data set as MYDATA.

Is there a workaround? Or is my memory wrong?

---

There is no work around.  If you are planning to use the USER lib functionality then you need to plan for when you should be using one level names and when to use two level names.  

[PaigeMiller]

---

@Tom wrote:

---

@PaigeMiller wrote:

Hello @ChrisHemedinger 

I have done this a long time ago, but as I recall, once you execute the command

options user=RES;

then any data sets in the WORK library now have to be referred to with two-level names, e.g. WORK.MYDATA; and so you can't just refer to this data set as MYDATA.

Is there a workaround? Or is my memory wrong?

---

There is no work around.  If you are planning to use the USER lib functionality then you need to plan for when you should be using one level names and when to use two level names.  

---

That was my conclusion too.

[Quentin]

Very interesting solution, @ChrisHemedinger , thanks.  I had never even noticed access=temp option.

I'm curious, if we change the question to a macro design question (hijacking the thread a bit), what others would think of using this USER= approach as a way to create a namespace for temporary datasets created by a macro.  Typically for utility macros I've used underscore prefixes or similar on dataset names when creating temp datasets inside a macro, and deleted them at the end.  But I hadn't thought about using the USER= option to redirect one level names to a dedicated directory and then KILLing it at the end.  I guess for this approach to work each macro would need a unique name for its user library.  Because when an OUTER macro calls an INNER macro, INNER would need an independent user library, and it would also need to be able to save and restore the value of OUTER user library.  But that doesn't seem like it would be hard. 

If INNER was passed a one-level name for an input dataset, it would also need a rule to determine which outer users libraries should be searched. Meaning, it should probably look in the user library of OUTER first to see if it exists, and then look in the session work library.  That could be a hassle to implement when you have multiple levels of nested macro calls, you would have (conceptually) nested user libraries.

But it seems like an appealing idea, to know that any one-level name datasets created by a macro are automatically written a dedicated namespace.

Any thoughts, @Tom @PaigeMiller @Astounding @yabwon ?

[PaigeMiller]

I would like to see this in a separate thread.

[Astounding]

@Quentin ,

Just a few related thoughts ...

You bring up legitimate cautions about what could happen when each macro has its own value for USER=.  One more that you hadn't mentioned:  What if an inner macro uses the same USER= library as an outer macro?  The outer macro's data sets would likely be erased (perhaps accidentally).

For a few reasons, I prefer to avoid this technique (but I have used it).  Here are some alternatives to consider.

Before running a macro, capture a snapshot of dictionary.tables.  When the macro ends, again capture a snapshot and compare it to the original snapshot.  Delete any additions.  Notice that this does not necessary limit the processing to the WORK library (which might be a blessing or a curse).  Technically, the original post doesn't limit the deletions to temporary data sets although that might very well have been the intention. 

Instead of creating a zillion temporary data sets, create just one and reuse a single name.  Modify the macro structure to then (if appropriate) append results in this one name to a larger, master output data set.

Or, delete data sets inside the macro once they are no longer needed.

I don't like to burden the user with obligations that put the data in a perilous position.  If the user has to ask this question in the first place, I would be hesitant to give the user the power to specify values for multiple USER= options.

Anyway, food for thought.

[yabwon]

@Quentin ,

I think that the functionality of "local macro library" can be achieved with help of MD5(footnote1). From what I've got on the fly (using @ChrisHemedinger idea with the USER library) is the following: 

0) set this at the begging of the code:

options dlcreatedir;
%let lib = WORK;

to allow create sub-directories inside the WORK directory, and create global macro variable LIB.

1) you need this little macro, for convenience:

%macro library(in);
_%sysfunc(MD5(%upcase(&in.)),hex7.)
%mend library;

to ensure 8 character long library names (macro name can be longer than 8, so we need to get some handy "shortcut".

Now:

2) add this "prefix"(header) to your macro code:

/* get external lib */
%local outlib;
%let outlib = &lib.;
/* set local user */
%local lib macroName;
%let macroName = &sysmacroname.; /* FOOTNOTE */
%let lib = %library(&macroName.);
libname &lib. "%sysfunc(pathname(work))/&lib.";
options user = &lib.;

3) and the following "suffix"(footer)

options user = &outlib.;

so the structure of the macro would be something like:

%macro myMacro(some,parameters);
/* header */
/* get external lib */
%local outlib;
%let outlib = &lib.;
/* set local user */
%local lib macroName;
%let macroName = &sysmacroname.;
%let lib = %library(&macroName.);
libname &lib. "%sysfunc(pathname(work))/&lib.";
options user = &lib.;

/**/
  /* your macro code goes here */
/**/

/* footer */
options user = &outlib.;
%mend myMacro;

Whenever you create a "libnameles" data set in such a macro, it will be stored in that "local" library generated for that particular macro. And Thanks to the %library() macro you can easy get the library for a given macro (see next example).

The:

%let macroName = &sysmacroname.;

part is needed because call of the form:

%library(&sysMacroName.);

resolves the sysMacroName macro variable to "library" (footnote example).

Bigger example would be:

options dlcreatedir;
%let lib = WORK;

%macro library(in);
_%sysfunc(MD5(%upcase(&in.)),hex7.)
%mend library;


%macro b_short(id);
/* get external lib */
%local outlib;
%let outlib = &lib.;
/* set local user */
%local lib macroName;
%let macroName = &sysmacroname.;
%let lib = %library(&macroName.);
libname &lib. "%sysfunc(pathname(work))/&lib.";
options user = &lib.;

/**/
  /* your macro code goes here */
  data dataset_B_&id.; /* that one goes to the "local" library */
    set sashelp.class;
  run;
/**/

options user = &outlib.;
%mend b_short;



%macro c_very_very_long(id);
/* header */
/* get external lib */
%local outlib;
%let outlib = &lib.;
/* set local user */
%local lib macroName;
%let macroName = &sysmacroname.;
%let lib = %library(&macroName.);
libname &lib. "%sysfunc(pathname(work))/&lib.";
options user = &lib.;

/**/
  /* your macro code goes here */
  %b_short(1);
  
  data dataset_C_&id.; /* that one goes to the "local" library */
    set sashelp.class;
  run;

  %b_short(3);


  data %library(b_short).dataset_C_&id.; /* that one goes to the "external" library */
    set sashelp.class;
  run;

/**/

/* footer */
options user = &outlib.;
%mend c_very_very_long;


options mprint mlogic;

%c_very_very_long(2)

All the best

Bart

Footnote 1: Wont work with 9.4M5 since M5 has a bug which doesn't allow to use MD5() in %sysfunc()

Footnote example, in open code:

%macro test(x);
%put *&=x.*;
%mend;

%put #&=sysmacroname.#;
%test(&sysmacroname.)

[Quentin]

Thanks @yabwon, good stuff.  I like the %library macro using MD5.  I think you've shown me something like that before... I'm not sure I like the idea of specifying which library a dataset should be written to. I was thinking of the following goals:

1. A macro should have its own local user library that will be used for creating and reading datasets with one-level names.
2. If a macro is passed a one-level dataset name as an argument, it should be interpreted as a reference to a dataset in the calling environment's user library (or WORK library if there is no user library specified in the calling environment).

This would not necessarily allow you to reference a dataset from any arbitrary user library, but I think it would meet my primary goal of avoiding name collisions for datasets.

If I call (in open code):

%inner(data=class,out=want)

I want CLASS to be read from the WORK library and WANT to be written to the WORK library, and any "internal" datasets to be written to the user library of inner.  Here's my attempt at that macro, using much of the code from you and Chris.  I'm not sure I really like my approach here:

%macro inner(data=,out=) ;

  /*Set up*/
  *calling lib is the libref used for one-level names in the environment that called the macro ;
  *could be WORK or a libref specified in the USER option ;
  %local holduser ;
  %let holduser=%sysfunc(getoption(user)) ;

  %local callinglib ;
  %if &holduser='' or &holduser=WORK %then %do ;
    %let callinglib=WORK ;
  %end ;
  %else %let callinglib=&holduser ;

  *If a dataset name passed in a parameter has only one level, make it a two level name with libref that points to the work/user library of the calling environment ;
  %if %sysfunc(findc(&data,.))=0 %then %let data=&callinglib..&data ;
  %if %sysfunc(findc(&out,.))=0 %then %let out=&callinglib..&out ;


  *create local user library for this macro ;
  %local lib macroName;
  %let macroName = &sysmacroname;
  %let lib = %library(&macroName);
  libname &lib "%sysfunc(pathname(work))/&lib";
  options user = &lib;


  /*main macro code*/
  data step1 ;  * written to local user library ;
    set &data ; * read from work/user library of the calling environment (if one-level name) ;
  run ;
  
  data &out ;   * written to work/user library of the calling environment (if one-level name) ;
    set step1 ; * read from local user library ;
  run ;


  /*cleanup*/
  proc datasets lib=&lib kill nodetails nolist; 
  quit;
  libname &lib CLEAR;
  options user=&holduser ;
%mend inner;

If I have an %OUTER macro which calls %INNER, I want any internal datasets created in OUTER to be written to its user library.  And if it passes a one-level dataset name to %INNER, I want %INNER to know that the dataset name is a reference to a dataset in the OUTER user library.  So %OUTER looks like below, with 90% of the code is the same setup and cleanup code used in %INNER:

%macro outer(data=,out=) ;

  /*Set up*/
  *calling lib is the libref used for one-level names in the environment that called the macro ;
  *could be WORK or a libref specified in the USER option ;
  %local holduser ;
  %let holduser=%sysfunc(getoption(user)) ;

  %local callinglib ;
  %if &holduser='' or &holduser=WORK %then %do ;
    %let callinglib=WORK ;
  %end ;
  %else %let callinglib=&holduser ;

  *If a dataset name has only one level, make it a two level name with libref that points to the work/user library of the calling environment ;
  %if %sysfunc(findc(&data,.))=0 %then %let data=&callinglib..&data ;
  %if %sysfunc(findc(&out,.))=0 %then %let out=&callinglib..&out ;

  *create local user library for this macro ;
  %local lib macroName;
  %let macroName = &sysmacroname;
  %let lib = %library(&macroName);
  libname &lib "%sysfunc(pathname(work))/&lib";
  options user = &lib;


  /*main macro code*/
  data stepA ;  * written to local work/user library ;
    set &data ; * read from work/user library of the calling environment (if one-level name) ;
  run ;

  %inner(data=stepA /*read from local work/user libary*/
        ,out=stepB  /*written to local work/user library*/
        )
  
  data &out ;   * written to work/user library of the calling environment (if one-level name) ;
    set stepB ; * read from local work/user library ;
  run ;


  /*cleanup*/
  proc datasets lib=&lib kill nodetails nolist; 
  quit;
  libname &lib CLEAR;
  options user=&holduser ;
%mend outer;

What I like about this approach is that the main macro code looks normal/readable to me.  I'm using one-level names for my temp datasets, and they'll created in a unique local library for each macro, so no collisions (as long as I don't write a recursive macro. : )    It also creates a bit of encapsulation.  With this setup, a macro cannot read any datasets from the another "scope" (WORK library or another macro's user library), or write any datasets to another scope, unless the one-level dataset names have been passed in as parameter arguments.  Other than values passed as parameters, any one level dataset name is seen as reference to a "local" dataset.

I don't like the amount of code needed for the setup, but it could be easily abstracted into a helper macro so that wouldn't be much of an issue.

A bigger concern is I think it would take me a while to become comfortable reading a log where most of the librefs were MD5 strings rather than human readable.

But there's something appealing about giving a macro its own scope to store temporary "local" datasets.

[yabwon]

@Quentin  few thoughts I have,

0) I think it is necessary to test stet USER=WORK (or any not null value) at the beginning

1) the part:

  %local callinglib ;
  %if &holduser='' or &holduser=WORK %then %do ;
    %let callinglib=WORK ;
  %end ;
  %else %let callinglib=&holduser ;  *If a dataset name passed in a parameter has only one level, make it a two level name with libref that points to the work/user library of the calling environment ;
  %if %sysfunc(findc(&data,.))=0 %then %let data=&callinglib..&data ;
  %if %sysfunc(findc(&out,.))=0 %then %let out=&callinglib..&out ;

can be simplified to:

  %let syslast=&data.;
  %let data=&syslast.;

  %let syslast=&out.;
  %let out=&syslast.;

(credit to @RichardDeVen for this trick).

3) From my experience I would keep the parameters for "data" and "out" as they are (in particular with properly prepared library assignments) because you could use them not wit one data set but with many, e.g.

%outer(data=sashelp.class1 sashelp.class2, out=class)

%local lib macroName;
  %let macroName = &sysmacroname;
  %let lib = %library(&macroName);
  libname &lib "%sysfunc(pathname(work))/&macroName"; /* <<<<<<<<<<<< */
  options user = &lib;

%outer(data=sashelp.class, out=classF(where(sex='F')) classM(where(sex='M')) ) 

and with many data sets both approach from 2) gets far more complicated to handle.

And in the "inner" macro call could be something like this:

%inner(data=%library(outer).stepA /*read from local work/user libary*/
       ,out=%library(outer).stepB  /*written to local work/user library*/
      )

or just:

%inner(data=&lib..stepA /*read from local work/user libary*/
       ,out=&lib..stepB /*written to local work/user library*/
      )

since the lib value is set for that macro (outer).

4) To make LOG more "readable" the library macro can be modified a bit:

%macro library(in);
%put NOTE: Libref for &in. is _%sysfunc(MD5(%upcase(&in.)),hex7.);
_%sysfunc(MD5(%upcase(&in.)),hex7.)
%mend library;

{EDIT} and also in the header it can be set like that:

%local lib macroName;
  %let macroName = &sysmacroname;
  %let lib = %library(&macroName);
  libname &lib "%sysfunc(pathname(work))/&macroName"; /* <<< */
  options user = &lib;

so the directory name is the same as macro name.

Bart

Full code for with 0), 1), and 2), and 4):

%macro library(in);
%put NOTE: Libref for &in. is _%sysfunc(MD5(%upcase(&in.)),hex7.);
_%sysfunc(MD5(%upcase(&in.)),hex7.)
%mend library;

options dlcreatedir mprint;
options user=work ;

%macro inner(data=,out=) ;

  /*Set up*/
  *calling lib is the libref used for one-level names in the environment that called the macro ;
  *could be WORK or a libref specified in the USER option ;
  %local holduser ;
  %let holduser=%sysfunc(getoption(user)) ;

  %let syslast=&data.;
  %let data=&syslast.;

  %let syslast=&out.;
  %let out=&syslast.;

  *create local user library for this macro ;
  %local lib macroName;
  %let macroName = &sysmacroname;
  %let lib = %library(&macroName);
  libname &lib "%sysfunc(pathname(work))/&macroName";
  options user = &lib;

  /*main macro code*/
  data step1 ;  * written to local user library ;
    set &data ; * read from work/user library of the calling environment (if one-level name) ;
  run ;
  
  data &out ;   * written to work/user library of the calling environment (if one-level name) ;
    set step1 ; * read from local user library ;
  run ;


  /*cleanup*/
  proc datasets lib=&lib kill nodetails nolist; 
  quit;
  libname &lib CLEAR;
  options user=&holduser ;
%mend inner;

%macro outer(data=,out=) ;

  /*Set up*/
  *calling lib is the libref used for one-level names in the environment that called the macro ;
  *could be WORK or a libref specified in the USER option ;
  %local holduser ;
  %let holduser=%sysfunc(getoption(user)) ;

  %let syslast=&data.;
  %let data=&syslast.;

  %let syslast=&out.;
  %let out=&syslast.;

  *create local user library for this macro ;
  %local lib macroName;
  %let macroName = &sysmacroname;
  %let lib = %library(&macroName);
  libname &lib "%sysfunc(pathname(work))/&macroName";
  options user = &lib;


  /*main macro code*/
  data stepA ;  * written to local work/user library ;
    set &data ; * read from work/user library of the calling environment (if one-level name) ;
  run ;

  %inner(data=&lib..stepA /*read from local work/user libary*/
         ,out=&lib..stepB  /*written to local work/user library*/
        )
  
  data &out ;   * written to work/user library of the calling environment (if one-level name) ;
    set stepB ; * read from local work/user library ;
  run ;


  /*cleanup*/
  proc datasets lib=&lib kill nodetails nolist; 
  quit;
  libname &lib CLEAR;
  options user=&holduser ;
%mend outer;

%outer(data=sashelp.class,out=class_new)

[Quentin]

Wow, thanks Bart.  That looks really nice.  That trick from Richard is pretty nifty.

In that version when you call %inner instead of calling like:

  %inner(data=&lib..stepA /*read from local work/user library*/
         ,out=&lib..stepB  /*written to local work/user library*/
        )

You could also call like:

  %inner(data=stepA /*read from local work/user library*/
         ,out=stepB  /*written to local work/user library*/
        )

which I like, because it means that temporary datasets that are "internal" to a macro can always be referenced with one-level names.

I would think a lot more about this before doing this in production, but it seems interesting to me.  

Thanks,

--Q.

[maguiremq]

You could give the datasets the same prefix like an underscore `_` followed by some pattern like `_mds` for 'macro data set'.

Then it's as simple as:

proc datasets library = work;
delete _mds:;
run;
quit;

Example:

proc sql;
	select
				distinct make
					into: makes separated by " "
	from
				sashelp.cars;
quit;

%macro dsmaker ();
%do i = 1 %to %sysfunc(countw(&makes., " "));
%let dsn = %scan(&makes., &i., " ");

proc sql;
	create table 	_mds_&dsn. as
		select
					*
		from
					sashelp.cars
		where
					make = "&dsn.";
quit;

%end;
%mend;

%dsmaker();

proc datasets library = work;
	delete _mds:;
run;
quit;

An error gets thrown because Mercedes-Benz has a hyphen in it. I'm ignoring that since it's just an example.

[ballardw]

Another approach would be get a data set of the datasets in the Work library prior to the macro run, again after the run (or possibly better as the last step of the macro), compare the two to create a delete list then delete them. However this may not be desired if you actually intentionally also create wanted output sets in the Work library, which is not clear from your description if that may be the case or not.

[maguiremq]

Thanks @ballardw -- I agree with you completely. Since we had limited information from the OP, I decided to make some contrived example that had a set of assumptions that I didn't address. If they provided us more information, I probably would not have gone this route.