Deleting dataset with no observations in macro

[deleted_user]

I am wondering how to automatically delete an entire dataset if there are no observations. The macro I created scrolls through an entire library and manipulates all the datasets within the library. However there are a few that have no observations and the macro still creates them as an ouput dataset. I don't want to use proc datasets and the specific dataset name to get rid of them because they may have data in them in the future. These datasets are causing errors later on in my code.

[deleted_user]

Refer the below link. It might be helpful for your situation.

http://www2.sas.com/proceedings/sugi26/p095-26.pdf

~ Sukanya E

[sbb]

The referenced paper makes mention of observation level deletion, not file deletion. Explore using DICTONARY.MEMBERS (SAS-maintained view) with PROC SQL to detect an empty SAS member and generate PROC DELETE code.

Have a look at the SAS support http://support.sas.com/ website for SAS-hosted documentation and technical/conference papers, using the SEARCH facility or Google advanced search with the parameter site:sas.com (limits the search).

Scott Barry
SBBWorks, Inc.

[deleted_user]

Hi,

Please try this

Proc sql;
select memname into:tables separated by " "
from dictionary.tables
where LIBNAME="library-name in capitals" and NOBS NE 0;
quit;

%put &tables.;

This code will create a macro with all the dataset names having atleast one observation.

We can use this macro for furthur use

[deleted_user]

Thanks to all who responded. Got it to work. Plus thanks for the pointers to other documentation for future reference

[DanielSantos]

Another approach would be to code a macro that will delete the dataset (name passed as argument) if it is indeed empty.

You see, no datastep code will execute if the dataset is empty.

So, if you try to assign a value to a macro var from within the datastep, this assignment will only occur if the dataset has at least one row.

Assume that the dataset is empty, let say:

%let EMPTY=1;

Then try to reset the macro var within the datastep;

data _null_;
set &DATA;
call symput('EMPTY','0'); /* this will only occur if &DATA is not empty */
run;

Now, you just need to check the macro var for its value at the end, to perform (or not) a proc dataset delete,

%if &EMPTY %then %do;
proc dataset lib=&LIB nolist;
delete &DATA; /* delete dataset */
quit;
%end;

Next, you just need to invoke the macro at every point where it is needed.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[Angela53]

Can you please tell me where I missed something? I need to run a bunch of macros that keep creating 9 temporary datasets, then delete the empty one before going to the next step of the calculations.

I tried your macro after I created the temp files. It checks how many observations but does not delete the empty ones.

Thanks!

%macro apaga;
%let EMPTY=1;
%do i=1 %to 9;
data _null_;
set x&i;
call symput('EMPTY','0'); /* this will only occur if &DATA is not empty */
run;
%end;
/*
Now, you just need to check the macro var for its value at the end, to perform (or not) a proc dataset delete,
*/
%if &EMPTY %then %do;
proc dataset lib=work nolist;
delete work.x&i; /* delete dataset */
quit;
%end;
%mend;
%apaga; run;

[Tom]

So as soon as you find one non-empty dataset you will never find any more empty datasets because EMPTY will never be set back to true. 

Move the %LET statement inside the %DO loop.  

[Angela53]

same results.. counts nobs but does not delete empties...

[Angela53]

same result with:

%macro apaga;
%do i=1 %to 9;
%let EMPTY=1;
data _null_;
set x&i;
call symput('EMPTY','0'); /* this will only occur if &DATA is not empty */
run;
%end;
/*
Now, you just need to check the macro var for its value at the end, to perform (or not) a proc dataset delete,*/

%if &EMPTY %then %do;
proc datasets lib=work nolist;
delete work.x&i;
%end;/*%end;***also tried put it all inside teh first 2 loops and the result was the same***/
quit;
%mend;
%apaga; run;

[SASKiwi]

How about trying this?

%if &EMPTY = 1 %then %do;
proc dataset lib=work nolist;
delete work.x&i; /* delete dataset */
quit;
%end;

[Angela53]

same result. If the variable x is binary, if x means the same as if x=1.

[SASKiwi]

This works for me:

data class;
  set sashelp.class (obs=0);
run;

%macro delete_table;

%let EMPTY=1;

data _null_;
set class;
call symput('EMPTY','0'); 
run;

%put empty = ∅

%if &EMPTY %then %do;
proc datasets lib=work nolist;
delete class;
run;
quit;
%end;

%mend delete_table;
%delete_table;

[Astounding]

Here's a variation that simplifies the logic, eliminating the complications.

%macro apaga;

   %do i=1 %to 9;
      data _null_;
         if done then call execute("proc delete data=x&i; run;");
         stop;
         set x&i end=done;
      run;
   %end;

%mend;

%apaga

By switching to PROC DELETE, the code also can be easier to use if your source data sets are in more than one library.

[yabwon]

one more:

data test;
set sashelp.class (obs=0);
run;


%macro delData(ds);
data _null_;
  if nobs=0 then call execute("proc delete data=&ds.; run;");
  stop;
  set &ds. nobs=nobs;
run;
%mend delData;

%delData(test)

🙂

Bart