Ways to Count the Number of Obs in a dataset and pass it into a macro variable...

Well…there are many ways of getting the observation count into a macro variable…but there a few pros and cons in those methods…

1. using sql with count(*)..
eg.

proc sql;

select count(*) into :macvar

from dsn;

quit;

pros: simple to understand and develop
cons: you need to read the dataset in its entirety which requires processing power here…

2. datastep
eg.

data new;

set old nobs=num;

call symputx('macvar',num);

run;

pros: simple to understand and develop
cons: you need to read the dataset in its entirety which requires processing power here..

3. use dictionary table / view sashelp.vtable..

proc sql;

select nobs into :macvar

from sashelp.vtable

where memname="DSN" and memtype"DATA" and libname="LIBLOC";

quit;

where the values of memname, memtype and libname represents ur dataset name, data type and libname the library where the dataset is located…

NOTE: the values of memname, memtype and libname should be in capital letters mandatorily…

pros: one of the better methods
cons: complex to understand

4. use attrn() function with nlobs option to get the obs count…used in the macro example below….

pros: one of the better methods….reusable..
cons: complex to understand…

Here’s a SAS macro that counts the Observations in a dataset passed as a parameter and stores the value in a macro variable named macrovar….

/*Define macro that counts obs in a SAS dataset*/

%macro RecsInDS(table,macvar);

%global &macvar;

%local handle rc;

%let handle = %sysfunc(open(&table)); /* Open the table with open() function*/

%if &handle %then

%do;

%let &macvar = %sysfunc(attrn(&handle, nlobs)); /* get the observation count into the macvar

macro variable using the function attrn() with nlobs as the option*/

%let rc = %sysfunc(close(&handle)); /* close the dataset with the close() function */

%end;

%PUT RecsInDS &table: &&&macvar.    &macvar=&&&macvar.; /* write the Record count to the Log */

%mend RecsInDS;

Now Call the Macro...

%RecsInDS(sashelp.air,obs);

The SAS Log
RecinDS sashelp.air: 144 obs=144