How to count variables for each dataset in a library

[BrahmanandaRao]

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 

[PaigeMiller]

What is wrong with the code you show?

[BrahmanandaRao]

[PaigeMiller]

NVAR is not a variable in DICTIONARY.COLUMNS.

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

[ballardw]

---

@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.

[Tom]

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 ;

[Reeza]

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. 

[Sajid01]

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;

[Shmuel]

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;

[BrahmanandaRao]

Using macros to get required result

[Shmuel]

@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 */

[Shmuel]

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;