Macro for deleting table constraints

Attention : This code requires administrator privileges.

The `mp_deleteconstraints` macro is designed to automate the process of deleting constraints from SAS^© tables. It takes as input a SAS^© dataset (`inds`) that contains detailed information about the constraints to target (such as the library, table name, and constraint name). After pre-sorting the input dataset, a `DATA STEP` iterates through this information to construct the corresponding `ALTER TABLE ... DROP CONSTRAINT` SQL statements. The execution of these statements is conditioned by the `execute` parameter's value. If `execute` is set to 'YES', the macro uses `CALL EXECUTE` to submit the SQL commands to the server, thus modifying the table structure. The output dataset (`outds`) contains the generated SQL statements, whether they were executed or not.

Data Analysis

Type : INTERNAL

The `mp_deleteconstraints` macro processes a SAS dataset that must be provided via the `inds=` parameter. This dataset is typically the result of another SAS procedure or macro (like `mp_getconstraints` mentioned in the header), which extracts existing SAS table constraint metadata. The example usage in the comments suggests that the input dataset can be created from temporary tables (`work.example`), indicating internal data management within the SAS system rather than direct import from external files.

1 Code Block

Macro Definition and PROC SORT

Explanation :
This block initializes the `mp_deleteconstraints` macro and defines its three parameters: `inds` for the input constraints dataset, `outds` for the sorted output dataset, and `execute` to control the execution of SQL commands. The `PROC SORT` procedure is then used to sort the input dataset (`&inds`) by the variables `libref`, `table_name`, and `constraint_name`. The sorted dataset is saved under the name specified by `&outds`.

Copied!

1	%macro mp_deleteconstraints(inds=mp_getconstraints
2	,outds=mp_deleteconstraints
3	,execute=NO
4	)//STORE SOURCE/;
5
6	PROC SORT DATA=&inds out=&outds;
7	BY libref table_name constraint_name;
8	RUN;

2 Code Block

DATA STEP

Explanation :
This `DATA STEP` processes the sorted dataset, generating an `ALTER TABLE ... DROP CONSTRAINT` SQL statement for each unique constraint (detected by `first.constraint_name`). The `drop_statement` variable is created to store this statement. If the macro's `execute` parameter is 'YES', the `CALL EXECUTE('proc sql;')` command is used only once at the beginning of the `DATA STEP` to open an SQL block, and `CALL EXECUTE(drop_statement)` executes each generated constraint deletion statement. The `DATA STEP` also writes the generated statements to the output dataset `&outds`.

Copied!

1	DATA &outds;
2	SET &outds;
3	BY libref table_name constraint_name;
4	LENGTH drop_statement $500;
5	IF _n_=1 and "&execute"="YES" THEN call execute('proc sql;');
6	IF first.constraint_name THEN DO;
7	drop_statement=catx(" ","alter table",libref,".",table_name
8	,"drop constraint",constraint_name,";");
9	OUTPUT;
10	IF "&execute"="YES" THEN call execute(drop_statement);
11	END;
12	RUN;
13
14	%mend mp_deleteconstraints;

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