Check the number of a data set

[liyongkai800]

Hello folks,

I'm looking for a way to check the number of rows for several data sets in a library.

Say I have Dsn1, Dsn2 and Dsn3 stored in a library called A, and I only want to execute the process on the data sets which has more than 10 rows.

I am using %if %sysfunc(attrn(A.Dsn&i,nobs))>10 %then %do; 

However, sas told me attrn referenced by %sysfunc is not a number. Any idea?

[PaigeMiller]

Show us the LOG for these steps, without you deleting anything. We need to see the code you are using, as seen in the log, and we need to see the exact error message, as seen in the log.

Please format the log so it will be readable. Click on the </> icon and then paste the log as text into the window that appears. DO NOT SKIP THIS STEP.

[mklangley · Accepted solution]

The first argument of the ATTRN() function is the dataset identifier, not a dataset name. Here is an example:

%let dsid=%sysfunc(open(sashelp.cars));
%if %sysfunc(attrn(&dsid,nobs))>10 %then %do;
    %put Yes, there are more than 10 records.;
%end;
%let rc=%sysfunc(close(&dsid));

For your scenario, where you have three datasets in a single library, you could use a loop within a macro to dynamically evaluate their row counts:

/* checking the row counts in work.test1, work.test2 and work.test3 */
%macro obs_count;
    %do i = 1 %to 3;
        %let dsid&i=%sysfunc(open(WORK.TEST&i));
        %if %sysfunc(attrn(&&dsid&i,nobs))>10 %then %do;
            %put Yes, there are more than 10 records in WORK.TEST&i;
        %end;
        %let rc=%sysfunc(close(&&dsid&i));
    %end;
%mend;
%obs_count;

 Edit: Close the datasets after completed. (Thanks for catching that, @Tom)

[Tom]

Don't forget to close the datasets you opened with the OPEN() function call.

[liyongkai800]

Thanks, you are right. The argument in the open function should not be a data set.

[Shmuel]

Alternative way is to check dictionary of sas by:

proc sql;
  select LIBNAME, MEMNAME, NOBS
  from dictionary.tables 
  where libname = "SASHELP" and nobs > 10;
quit;

[Kurt_Bremser]

Query DICTIONARY.TABLES (SQL) or SASHELP.VTABLE (data/proc step).