Deleting Macros from work.sasmacr Catalog by SQL Pattern

Attention : This code requires administrator privileges.

This macro, `delmac`, is designed to delete existing SAS^© macros from the `work.sasmacr` catalog based on a 'like' SQL pattern provided by the user. It first converts the 'like' pattern to uppercase and replaces colons (':') with percentage signs ('%') for compatibility with SQL wildcards. Then, it lists all macros in the `work.sasmacr` catalog into a temporary dataset named `_listmac`. A `PROC SQL` step filters this dataset to identify macros matching the pattern and stores their names in a macro variable `delmac`. The temporary dataset `_listmac` is then deleted. Finally, if macros have been identified, `PROC CATALOG` is used to delete them from `work.sasmacr`. The macro includes basic error handling for an empty 'like' parameter and restores the `notes` option to its original state. The 'like' pattern supports SQL wildcards '_' (any single character) and '%' (any string), with '\' used to escape '_'.

Data Analysis

Type : MIXTE

The code operates on the `work.sasmacr` catalog of the current SAS session, which contains compiled macros. It creates a temporary dataset `_listmac` in the `WORK` library to store the list of macros before filtering them with `PROC SQL`. No external data is read or required for its operation.

1 Code Block

MACRO DEFINITION

Explanation :
This block defines the `delmac` macro with a `like` parameter. It initializes local variables, captures the current state of the SAS `notes` option and disables it for the duration of the macro. It also validates the `like` parameter and pre-processes it for SQL search by converting it to uppercase and replacing ':' with '%'.

Copied!

1	%put MACRO CALLED: delmac v1.0;
2
3	%macro delmac(like);
4	%local delmac err savopts;
5
6	%let err=ERR%str(OR);
7	%let savopts=%sysfunc(getoption(notes));
8
9	options nonotes;
10
11	%IF not %LENGTH(&like) %THEN %goto exit;
12
13	%let like=%upcase(%sysfunc(translate(&like,%,:)));

2 Code Block

PROC CATALOG Data

Explanation :
This block uses `PROC CATALOG` to list all 'macro' type entries from the `work.sasmacr` catalog and writes them to a temporary dataset named `_listmac`.

Copied!

1
2	PROC CATALOG catalog=work.sasmacr entrytype=macro;
3	contents out=_listmac;
4
5	QUIT;
6

3 Code Block

PROC SQL

Explanation :
This `PROC SQL` block selects macro names from the temporary dataset `_listmac` that match the pattern provided by the `like` parameter. The found macro names are concatenated into the macro variable `delmac`, separated by spaces. The `escape '\'` option allows using the '\' character to escape SQL wildcards ('_').

Copied!

1	PROC SQL noprint;
2	select name into :delmac separated BY " " from _listmac
3	where name like "&like" escape '\';
4	QUIT;

4 Code Block

PROC DATASETS

Explanation :
This block uses `PROC DATASETS` to delete the temporary dataset `_listmac`, which is no longer needed after extracting macro names.

Copied!

1
2	PROC DATASETS nolist;
3	delete _listmac;
4
5	QUIT;
6

5 Code Block

PROC CATALOG

Explanation :
This conditional block checks if the macro variable `delmac` contains macro names (i.e., if any macros matching the pattern were found). If so, `PROC CATALOG` is used to delete these macros from the `work.sasmacr` catalog.

Copied!

1	%IF %LENGTH(&delmac) %THEN %DO;
2	PROC CATALOG catalog=work.sasmacr entrytype=macro;
3	delete &delmac;
4	QUIT;
5	%END;

6 Code Block

MACRO LOGIC

Explanation :
This block handles the macro's exit logic. It includes an `%exit:` label for error handling (if no 'like' pattern is provided) and a `%skip:` label for normal execution completion. Finally, it restores the SAS `notes` option to its original value, ensuring the macro does not permanently affect the SAS environment.

Copied!

1	%goto skip;
2	%exit: %put &err: (delmac) No "like" string supplied;
3	%skip:
4
5	options &savopts;
6
7	%mend delmac;

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.

Copyright Info : This is public domain software. No guarantee as to suitability or accuracy is given or implied. User uses this code entirely at their own risk.

Retour à la liste