delete a data set if number of rows=0

[Ronein]

Hello

What is the way to delete a data set if number of rows=0 ?

[Kurt_Bremser]

The number of observations is stored in column nobs in dictionary.tables (SQL).

Once you have a dataset with dataset names, you can use call execute from that dataset to create a proc delete step.

[novinosrin · Accepted solution]

Hi @Ronein   Here is a demo

/*Create a sample with 0 rows*/
data w;
stop;
set sashelp.class;
run;

/*Solution*/
%macro t;
data _null_;
 call symputx('n',n);
 stop;
 set w nobs=n;
run;
%put &n;
%if &n=0 %then %do;
 proc delete data=w;
 run;
%end;
%mend t;

%t

[Ronein]

May you please explain the need of "STOP"  in your code?

I know that nobs is automatic variable that contains number of rows in data set names in set statement.

I understand that  in code nobs=n we place the value of nobs in variable n.

I also understand that using call symputx we put the value of variable n in  a macro variable called also n.

Why do we need to use "Stop"?

data _null_;
 call symputx('n',n);
 stop;
 set w nobs=n;
run;

[Kurt_Bremser]

Without stop, the data step would read the whole dataset, which is not necessary and would be a waste of time and resources.

[r_behata]

data h;
	set sashelp.class;
	stop;
run;


%let id=%sysfunc(open(h));
%let nobs=%sysfunc(attrn(&id,nobs));
%let cl=%sysfunc(close(&id));

%put &=nobs;


%macro chck;
%if &nobs eq 0 %then %do;
	proc delete data=h;
	run;
%end;
%mend;

%chck

[Ronein]

Thank you.

In  the macro that you create I didn't see using of the macro variables "ID"  and "cl".

May you explain please why did you create them and where did you use them in order to delete data set with 0 observations?  

[Kurt_Bremser]

---

@Ronein wrote:

Thank you.

In  the macro that you create I didn't see using of the macro variables "ID"  and "cl".

May you explain please why did you create them and where did you use them in order to delete data set with 0 observations?  

---

id is used in the attrn function, cl is needed to receive the return value of the close function. It has no other use.