BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
17 REPLIES 17
deleted_user
Not applicable
Refer the below link. It might be helpful for your situation.

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

~ Sukanya E
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Not applicable
Thanks to all who responded. Got it to work. Plus thanks for the pointers to other documentation for future reference
DanielSantos
Barite | Level 11
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
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7
same results.. counts nobs but does not delete empties...
Angela53
Obsidian | Level 7

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
PROC Star

How about trying this?

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

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

SASKiwi
PROC Star

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
PROC Star

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
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 8710 views
  • 3 likes
  • 9 in conversation