topic Re: How to count variables for each dataset in a library in SAS Programming

How to count variables for each dataset in a library

BrahmanandaRao — Thu, 18 Mar 2021 13:43:15 GMT

proc sql;
select catx("-",memname,nvar) into: x separated by ","
from dictionary.columns
where libname='FIRST' and memtype="DATA";
quit;

%put &x;

I want all variables for each dataset in FIRST libraray

Re: How to count variables for each dataset in a library

PaigeMiller — Thu, 18 Mar 2021 13:50:30 GMT

What is wrong with the code you show?

Re: How to count variables for each dataset in a library

BrahmanandaRao — Thu, 18 Mar 2021 13:54:18 GMT

Re: How to count variables for each dataset in a library

PaigeMiller — Thu, 18 Mar 2021 13:59:18 GMT

NVAR is not a variable in DICTIONARY.COLUMNS.

What are you trying to do by using this non-existent NVAR variable?

Re: How to count variables for each dataset in a library

Tom — Thu, 18 Mar 2021 14:04:01 GMT

Since you are retrieving one observation per variable just use GROUP BY and COUNT(*).

proc sql noprint ;
  select catx("-",memname,count(*))
    into :x separated by ","
  from dictionary.columns
  where libname='WORK' and memtype="DATA"
  group by memname
  ;
quit;
%put &=sqlobs &=x ;

Re: How to count variables for each dataset in a library

ballardw — Thu, 18 Mar 2021 14:31:32 GMT

@BrahmanandaRao wrote:

NVAR is in Dictionary.Tables not Dictionary.Columns. Each entry in Dictionary.columns is a single variable, so the "number"

would always be 1.

I shudder to think just what you intend to do with such a macro variable.

Re: How to count variables for each dataset in a library

Reeza — Thu, 18 Mar 2021 15:28:35 GMT

If you do a search of this topic on here you'll find another little nifty macro like the one I linked you to yesterday that creates a code book or documents all the metadata for your tables in a nice form, with many options for customization.

Re: How to count variables for each dataset in a library

Sajid01 — Thu, 18 Mar 2021 15:44:03 GMT

Hello @BrahmanandaRao

Your question is "How to count variables for each dataset in a library".
While there are many approaches to the solution, I would use the following code which I find simple and pretty straight forward.
I hope you will find it useful.
It creates two column output, the first is name of the dataset and the second the number of columns in the table.
Here is the code.

proc sql;
select memname Label "Dataset" ,count(name) as Num_of_cols
from dictionary.columns
where memtype="DATA"
group by memname ;
quit;

Re: How to count variables for each dataset in a library

Shmuel — Thu, 18 Mar 2021 16:23:05 GMT

It seems you wat to select from dictionary.tables and not dictionary.columns.

Run next code for example:

proc sql;
   select memname, nvar
   from dictionary.tables
   where libname = "SASHELP";
quit;

Re: How to count variables for each dataset in a library

BrahmanandaRao — Thu, 18 Mar 2021 22:12:58 GMT

Using macros to get required result

Re: How to count variables for each dataset in a library

Shmuel — Fri, 19 Mar 2021 00:47:26 GMT

@BrahmanandaRao , your subject is "How to count variables for each dataset in a library".

You can covert the code I sent to a macro in order to get the information for any desired dataset in a given library:

%macro count(lib=,dsname=);
  proc sql;
    select memname, nvar
   from dictionary.tables
   where libname = %upcase("&lib") and
               memname = %upcase("&dsname");
 quit;
%mend count;
%count(lib=sashelp, dsname=class); /* this is an example of using the macro */

Re: How to count variables for each dataset in a library

Shmuel — Fri, 19 Mar 2021 00:52:46 GMT

What kind of result you expect?
If you prefer a macro-variable then:
%macro count(lib=,dsname=);
proc sql noprint;

%global nvar;
select nvar into :nvar
from dictionary.tables
where libname = %upcase("&lib") and
memname = %upcase("&dsname");
quit;
%mend count;
%count(lib=sashelp, dsname=class);
%put NVAR=&nvar;