How Many Observations Are In This Data Set

This postal service explains how to determine the number of observations in a SAS dataset. Most of the times we need to check whether a SAS dataset is empty or not. In macro, nosotros generally tell SAS to go to the next iteration only when SAS dataset is non-empty. In this post, we will see various methods to count number of rows (records) in SAS table.

Method I : Proc SQL Count (Not Efficient)

In the example below, nosotros will employ CARS dataset from SASHELP library. This dataset contains 428 observations and 15 columns.

The easiest method is to use count(*) in Proc SQL. It returns all rows (missing plus non-missing rows) in a dataset.

proc sql;
select count(*) as N from sashelp.cars;
quit;

Result : 428

In case you want to store it in a macro variable, you tin utilise INTO : keyword.

proc sql noprint;
select count(*) into :N from sashelp.cars;
quit;
%put &Due north;

This volition impress the number of records in SAS log. Check log after running the above programme.

Is it an efficient method?

No, it is not efficient at all. It does not utilise metadata information of SAS dataset. Instead it reads through each record (row) of your SAS dataset. Information technology takes a long time to do it in big SAS tables. However, it is a simple and handy trick to calculate the number of rows in a SAS dataset.

Method 2 : Descriptor Portion (Efficient)

Before getting into item, we demand to sympathize the descriptor portion and how information technology works -

SAS dataset consists of the following ii portion -

Descriptor portion. It constitutes information nearly name of dataset, number of observations and variables, creation date, engine type.
Data portion. It stores values of information.

This method is ane of the most efficient fashion to count observations in a SAS tabular array as it uses metadata information and does non search in dataset.

data _NULL_;
if 0 and so set sashelp.cars nobs=n;
put "no. of observations =" n;
stop;
run;

Explanation

The 'if 0' statement does not procedure at execution time because IF statement does non hold True. The whole IF THEN statement is used to pull the header data of the data set and later paw over to the compiler to arrange it to the PDV.
NOBS is a SAS automatic variable which contains the number of rows in a dataset i.e. SASHELP.CARS dataset.
NOBS = N puts the returns count of records in the variable n.
The Stop statement is used to stop an endless loop.

Like the beginning method, we can keep it in a macro variable. See the implementation below -

information _NULL_;
if 0 then set sashelp.cars nobs=n;
call symputx('totobs',northward);
stop;
run;
%put no. of observations = &totobs;

SAS Output

CALL SYMPUT is one of the method to create a SAS macro variable in data stride. In this case, we have used a newer role i.eastward. Telephone call SYMPUTX which left justifies and trims trailing blanks from a numeric value. If y'all want to stick to the old style Telephone call SYMPUT, you tin can write like below -

call symput('totobs',left(due north));

three. Proc SQL Dictionary Method (Efficient)

Like second method, nosotros can use metadata information of a dataset with PROC SQLDictionary.Tables.

proc sql noprint;
select nobs into :totobs separated past ' ' from lexicon.tables
where libname='SASHELP' and memname='CARS';
quit;
%put total records = &totobs.;

Proc SQL Dictionary.Tables

It is an efficient method as it does not look into each values of a dataset to make up one's mind the count. The LIBNAME= refers to the name of the library in which data is stored. The MEMNAME= refers to SAS table (dataset). The separated by ' ' is used in this example to left align the numeric value.

four. Macro Language Method (Efficient)

This method also uses metadata data but information technology is via the macro language using Data step functions. The OPEN role is used to open a information. The ATTRN function returns the value of a numeric attribute for a SAS data set. When information technology is used with the NOBS statement, it returns the number of observations. Subsequently we are closing the opened dataset using Close function.

%macro totobs(mydata);
%let mydataID=%sysfunc(OPEN(&mydata.,IN));
%let NOBS=%sysfunc(ATTRN(&mydataID,NOBS));
%let RC=%sysfunc(Close(&mydataID));
&NOBS
%mend;
%put %totobs(sashelp.cars);

SAS : Check if it is empty table

Suppose you just need to cheque whether a table is empty or not. Y'all can use the same logic as explained to a higher place. And if the returned value is 0, write 'Empty Data' in log. Otherwise, count the number of records.

data _NULL_;
if 0 then set sashelp.cars nobs=n;
if north = 0 then put 'empty dataset';
else put 'Not empty. Total records=' n;
end;
run;

Result : Not Empty. Full records = 428

Let's create a bare dataset to check the above code. The following program returns empty dataset as 1=two condition does not meet.

proc sql noprint;
create tabular array temp as
select * from sashelp.cars
where 1 = 2;
quit;

Try it yourself!

Allow'southward wrap the to a higher place code in a SAS macro

%macro emptydataset (inputdata=);
data _NULL_;
if 0 and so set &inputdata. nobs=northward;
telephone call symputx('totobs',n);
stop;
run;
%if &totobs. = 0 %then %put Empty dataset;
%else %exercise;
%put TotalObs=&totobs;
%end;
%mend;

%emptydataset(inputdata=sashelp.cars);
Result : TotalObs=428

%emptydataset(inputdata=work.temp);

Outcome : Empty dataset

If y'all think it'south difficult to memorize sas code of descriptor portion method, you can use the code below.

data _NULL_;
set sashelp.cars nobs=N;
if _N_ = 2 then end;
put N;
run;

SAS log

It reads only first two observations from the dataset. See log in a higher place.

Near Writer:

Deepanshu founded ListenData with a uncomplicated objective - Make analytics easy to understand and follow. He has over x years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.