Search code examples
sqlsas

Loop to Count how many records in the table

I was wondering whether there’s a way to count the volume where the table name end with G in a loop and name it as the table name e.g output_dps_BCS_FT_G

enter image description here

I’m just wondering whether there is a efficient way of doing it rather than manually doing it one by one.

Solution

  • One approach is to use SAS dictionary tables, which store metadata about all datasets in defined libraries. You would likely want to add a WHERE clause to limit this to the libraries of interest to you. Code like:

    proc sql ;
  select libname,memname,nlobs from dictionary.tables
  where memname like '%G'
  ;
quit ;

    To output the results to a SAS dataset, you can add a CREATE TABLE clause, e.g.:

    proc sql ;
  create table G_Tables as
  select libname, memname as Filename, nlobs as Count 
  from dictionary.tables
  where memname like '%G'
  ;
quit ;

proc print data=G_Tables;
run;