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;