_delete_empty_tables

[Assaf_Attas]

אוהבים סדר וניקיון? 🙂

לפעמים מצטברות בספרייה מסוימת לא מעט טבלאות אשר אינן מכילות תצפיות..

המאקרו הבא משמש למחיקה גורפת של כל הטבלאות הריקות תחת ספרייה מסוימת.

%macro _delete_empty_tables(my_lib);

	%macro dummy;%mend dummy;

	/* Obtain option and temporarily turn off */
	%local user_source user_notes;
	%let user_source=%sysfunc(getoption(source));
	%let user_notes =%sysfunc(getoption(notes));

	option nosource nonotes;

	/*----------------------------------------------------------------------------------
		Declare local variables 
	  ----------------------------------------------------------------------------------*/
	%local _n_ds _i cur_ds cur_dm n_obs;

	/*----------------------------------------------------------------------------------
		Prepare list of all datasets in library 
	  ----------------------------------------------------------------------------------*/
	proc contents data=&my_lib.._all_ noprint nodetails directory out= work._ds_list (where=(MEMTYPE='DATA' and memname ne '_DS_LIST'));run;
	proc sort data= work._ds_list (keep=libname memname) nodupkeys;by libname memname;run;

	/*----------------------------------------------------------------------------------
		Determine number of datasets 
	  ----------------------------------------------------------------------------------*/
	proc sql noprint;
		select count(*) into: _n_ds
		from _ds_list;
	quit;
	%put *** Libref: "&my_lib" Contains: &_n_ds datasets;
	%put ***;

	/*----------------------------------------------------------------------------------
		Itterate on all tables 
	  ----------------------------------------------------------------------------------*/
	%do _i=1 %to &_n_ds;

		/*----------------------------------------------------------------------------------
			Point single table 
	  	  ----------------------------------------------------------------------------------*/
		data _null_;
			rec_num=&_i;
			set _ds_list point=rec_num;
			call symput("cur_ds",cats(libname,'.',memname));
			call symput("cur_dm",strip(memname));
			stop;
		run;

		/*----------------------------------------------------------------------------------
			Count observations in table 
	  	  ----------------------------------------------------------------------------------*/
		proc sql noprint;
			select count(*) into: n_obs from &cur_ds.;
		quit;
		%put *** Dataset #&_i (&cur_ds) has: &n_obs Observations;

		/*----------------------------------------------------------------------------------
			Delete if empty 
	  	  ----------------------------------------------------------------------------------*/
		%if %eval(&n_obs + 0) =0 %then %do;
			%put *** Deleteing empty table: &cur_ds;
			proc sql noprint;
				drop table &cur_ds;
			quit;
		%end;

	%end;

	/*----------------------------------------------------------------------------------
		Clean list file & reload user options 
	  ----------------------------------------------------------------------------------*/
	proc sql noprint;
		drop table work._ds_list;
	quit;

	options &user_source &user_notes;

%mend;