topic Re: Deleting dataset with no observations in macro in SAS Programming

Deleting dataset with no observations in macro

deleted_user — Wed, 17 Jun 2009 19:35:27 GMT

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.

Re: Deleting dataset with no observations in macro

deleted_user — Wed, 17 Jun 2009 20:15:03 GMT

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

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

~ Sukanya E

Re: Deleting dataset with no observations in macro

sbb — Wed, 17 Jun 2009 21:34:21 GMT

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.

Re: Deleting dataset with no observations in macro

deleted_user — Thu, 18 Jun 2009 11:25:21 GMT

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

Re: Deleting dataset with no observations in macro

deleted_user — Thu, 18 Jun 2009 14:01:57 GMT

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

Re: Deleting dataset with no observations in macro

DanielSantos — Thu, 18 Jun 2009 14:13:03 GMT

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

Re: Deleting dataset with no observations in macro

Angela53 — Thu, 13 Apr 2023 23:06:31 GMT

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;

Re: Deleting dataset with no observations in macro

Tom — Thu, 13 Apr 2023 23:20:39 GMT

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.

Re: Deleting dataset with no observations in macro

SASKiwi — Thu, 13 Apr 2023 23:25:52 GMT

How about trying this?

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

Re: Deleting dataset with no observations in macro

Angela53 — Thu, 13 Apr 2023 23:59:29 GMT

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

Re: Deleting dataset with no observations in macro

Angela53 — Thu, 13 Apr 2023 23:59:32 GMT

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

Re: Deleting dataset with no observations in macro

Angela53 — Fri, 14 Apr 2023 00:03:07 GMT

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;

Re: Deleting dataset with no observations in macro

SASKiwi — Fri, 14 Apr 2023 00:40:43 GMT

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 = &empty;

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

%mend delete_table;
%delete_table;

Re: Deleting dataset with no observations in macro

Astounding — Fri, 14 Apr 2023 02:11:48 GMT

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.

Re: Deleting dataset with no observations in macro

Kurt_Bremser — Fri, 14 Apr 2023 04:51:38 GMT

You need to perform the delete in the loop.

%macro apaga;
%do i =1 %to 9;
  proc sql noprint;
  select nobs into :nobs
  from dictionary.tables
  where libname = "WORK" and memname = "X&i.";
  quit;
  %if &nobs. = 0
  %then %do;
    proc delete data=work.x&i.;
    run;
  %end;
%end;
%mend;

Re: Deleting dataset with no observations in macro

Angela53 — Mon, 17 Apr 2023 16:34:22 GMT

Thanks, Kurt. Your correction works perfectly!

Re: Deleting dataset with no observations in macro

Angela53 — Mon, 17 Apr 2023 16:34:59 GMT

Thanks, it works perfectly!

Re: Deleting dataset with no observations in macro

yabwon — Mon, 17 Apr 2023 17:11:24 GMT

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