Check if the table is empty

Reply
N/A
Posts: 0

Check if the table is empty

Hi,

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

Thanks
N/A
Posts: 0

Re: Check if the table is empty

I have a solution! I think this is the best one Smiley Happy

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;
Frequent Contributor
Posts: 95

Re: Check if the table is empty

Here's an alternate solution:

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

Re: Check if the table is empty

That's cool, but I don't like to be depended of this "dictionary"... Ofcourse, it's up to you... Smiley Happy
Frequent Contributor
Posts: 91

Re: Check if the table is empty

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

Re: Check if the table is empty

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.
Contributor
Posts: 49

Re: Check if the table is empty

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
SAS Super FREQ
Posts: 8,820

Re: Check if the table is empty

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
New Contributor
Posts: 3

Re: Check if the table is empty

Am try outed this but it shows as "There are no records in a dataset" also when the data set contain observation
Ask a Question
Discussion stats
  • 8 replies
  • 7727 views
  • 0 likes
  • 6 in conversation