DATA Step, Macro, Functions and more

Deleting dataset with no observations in macro

Reply
N/A
Posts: 0

Deleting dataset with no observations in macro

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.
N/A
Posts: 0

Re: Deleting dataset with no observations in macro

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

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

~ Sukanya E
Super Contributor
Super Contributor
Posts: 3,174

Re: Deleting dataset with no observations in macro

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.
N/A
Posts: 0

Re: Deleting dataset with no observations in macro

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
N/A
Posts: 0

Re: Deleting dataset with no observations in macro

Thanks to all who responded. Got it to work. Plus thanks for the pointers to other documentation for future reference
Super Contributor
Posts: 474

Re: Deleting dataset with no observations in macro

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
Ask a Question
Discussion stats
  • 5 replies
  • 3245 views
  • 0 likes
  • 3 in conversation