Macro to create SQL dictionary views

The `%mp_dictionary` macro automatically generates a set of SAS^© views based on SAS^© SQL dictionary tables. It takes a 'lib' parameter to specify the libref where the views will be created, with 'WORK' as the default value. The process begins by creating a view named '&lib..dictionaries' from 'dictionary.dictionaries'. Then, it extracts the member names of this view into a macro list. A loop is used to iterate through this list and create a distinct view ('&lib..{viewName}') for each SQL dictionary entry, thus providing easy access to detailed information about tables, columns, indexes, etc., directly through SAS^© datasets.

Data Analysis

Type : SASHELP

The source data used by this macro comes exclusively from SAS's internal SQL dictionary views (`dictionary.dictionaries` and `dictionary.views`). These views are built-in system tables that provide metadata about the SAS environment and do not require any external data source or explicit data creation in the script.

1 Code Block

PROC SQL Data

Explanation :
This code block defines the `%mp_dictionary` macro. It uses `PROC SQL` to interact with the SAS dictionary. First, a temporary view named 'dictionaries' is created in the specified library (&lib) from 'dictionary.dictionaries'. This view contains a list of all available views in the SQL dictionary. Then, a `SELECT DISTINCT` is executed to extract the names of these views into a macro variable 'list'. A `%do` loop iterates over this list, and for each view name ('&mem'), a new view is created in the specified library. This allows each SQL dictionary view to be transformed into an accessible view in the chosen SAS library.

Copied!

1	%macro mp_dictionary(lib=WORK)//STORE SOURCE/;
2	%local list i mem;
3	PROC SQL noprint;
4	create view &lib..dictionaries as select * from dictionary.dictionaries;
5	select distinct memname into: list separated BY ' ' from &lib..dictionaries;
6	%DO i=1 %to %sysfunc(countw(&list,%str( )));
7	%let mem=%scan(&list,&i,%str( ));
8	create view &lib..&mem as select * from dictionary.&mem;
9	%END;
10	QUIT;
11	%mend mp_dictionary;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste