In our last post SAS tips & tricks #3 – SAS dictionary tables, we looked at how the dictionary tables can be used to find metadata about the SAS session, including dataset and variable level metadata.

In this post we look at how we can use a simple piece of SQL to create a histogram which gives a quick overview of the number of variables in each domain.

The SASHELP.VCOLUMN dataset contains a list of all variables present in each dataset. Here we use the repeat function to output a “*” character for each variable in the dataset. The output of the query is printed to the active output destination.

PROC SQL;
   SELECT memname  , REPEAT('*',nvar) AS freq
   FROM sashelp.vtable
   WHERE libname = "SASHELP";
 QUIT;

The result is something similar to the below, which gives a quick insight into the number of variables in each dataset in the SASHELP library.

This SQL histogram technique has a wide varierty of other applications, for example here we use it to show the frequency of countries within each region in the SASHELP.DEMOGRAPHICS dataset.

PROC SQL;
  SELECT region , REPEAT('*',COUNT(*)) AS freq
  FROM sashelp.demographics
  GROUP BY region
  ORDER BY region;
QUIT;