topic Re: delete a data set if number of rows=0 in SAS Programming

delete a data set if number of rows=0

Ronein — Sun, 29 Dec 2019 17:52:06 GMT

Hello

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

Re: delete a data set if number of rows=0

Kurt_Bremser — Sun, 29 Dec 2019 18:00:48 GMT

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.

Re: delete a data set if number of rows=0

novinosrin — Sun, 29 Dec 2019 18:18:24 GMT

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

Re: delete a data set if number of rows=0

r_behata — Sun, 29 Dec 2019 18:40:26 GMT

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

Re: delete a data set if number of rows=0

Ronein — Sun, 29 Dec 2019 20:33:23 GMT

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?

Re: delete a data set if number of rows=0

Ronein — Sun, 29 Dec 2019 20:48:17 GMT

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;

Re: delete a data set if number of rows=0

Kurt_Bremser — Sun, 29 Dec 2019 21:57:27 GMT

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

Re: delete a data set if number of rows=0

Kurt_Bremser — Sun, 29 Dec 2019 22:00:15 GMT

@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.