topic Re: Check if the table is empty in SAS Health and Life Sciences

Check if the table is empty

deleted_user — Mon, 21 Jan 2008 12:06:33 GMT

Hi,

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

Thanks

Re: Check if the table is empty

deleted_user — Mon, 21 Jan 2008 14:46:55 GMT

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;

Re: Check if the table is empty

1162 — Mon, 21 Jan 2008 15:27:51 GMT

Here's an alternate solution:

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

Re: Check if the table is empty

deleted_user — Mon, 21 Jan 2008 15:53:05 GMT

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

Re: Check if the table is empty

advoss — Tue, 22 Jan 2008 17:23:52 GMT

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.

Re: Check if the table is empty

deleted_user — Wed, 23 Jan 2008 06:30:32 GMT

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.

Re: Check if the table is empty

LawrenceHW — Fri, 25 Jan 2008 10:38:50 GMT

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

Re: Check if the table is empty

Cynthia_sas — Fri, 25 Jan 2008 17:38:09 GMT

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

Re: Check if the table is empty

Akhil_Vijayan — Fri, 07 Jul 2017 10:54:49 GMT

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