Check if the table is empty

[deleted_user]

Hi,

I'm looking for a simple solution to check is table empty or not.

Thanks

[deleted_user]

I have a solution! I think this is the best one 🙂

data _null_;
call symput('obscnt',0);
set work.out;
call symput('obscnt',_n_);
stop;
run;

%If &obscnt=0 %then %do;
%put 'There are no records in a dataset.';
%end;

[1162]

Here's an alternate solution:

proc sql;
select "# of Obs in WORK.OUT: ", nobs
from dictionary.tables
where libname='WORK'
and memname = 'OUT';
quit;

[deleted_user]

That's cool, but I don't like to be depended of this "dictionary"... Ofcourse, it's up to you... 🙂

[advoss]

Why don't you like to depend upon the "dictionary" tables? Unless you are using where clause in a proc or data step, the information provided by the dictionary tables is as good as SAS is.

[deleted_user]

Some people seem to have the impression that querying the Dictionary views is a slow process. In all cases I have seen, this is a result of poorly constructed SQL code and a SAS session that includes foreign (Sql server, Oracle, DB2 etc) libraries.

A well constructed SQL query such as that shown should have no performance issues.

[LawrenceHW]

If people don't like querying dictionary tables (and I don't understand why not) you can always use the following code:

%LET dsid=%SYSFUNC(OPEN(work.dataset));
%LET nobs=%SYSFUNC(ATTRN(&dsid.,NOBS));
%LET rc=%SYSFUNC(CLOSE(&dsid.));

The number of observations is then held in a macro variable [NOBS].

%IF &Nobs. EQ 0 %THEN %DO; .... %END; etc. etc. Message was edited by: LawrenceHW

[Cynthia_sas]

This is an elegant solution and one that I use a lot.

Just one warning that any %IF logic cannot be in open code -- but must be in a SAS Macro program.

cynthia

[Akhil_Vijayan]

Am try outed this but it shows as "There are no records in a dataset" also when the data set contain observation