topic Re: Check the number of a data set in SAS Programming

Check the number of a data set

liyongkai800 — Mon, 06 Jul 2020 15:17:34 GMT

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?

Re: Check the number of a data set

PaigeMiller — Mon, 06 Jul 2020 15:34:51 GMT

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.

Re: Check the number of a data set

mklangley — Mon, 06 Jul 2020 16:19:09 GMT

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)

Re: Check the number of a data set

Shmuel — Mon, 06 Jul 2020 15:55:00 GMT

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;

Re: Check the number of a data set

Tom — Mon, 06 Jul 2020 16:06:27 GMT

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

Re: Check the number of a data set

Kurt_Bremser — Mon, 06 Jul 2020 18:06:59 GMT

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

Re: Check the number of a data set

liyongkai800 — Tue, 07 Jul 2020 12:15:10 GMT

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