Attention : This code requires administrator privileges.
This script connects to the SAS© metadata server to search for 'ForeignKey' type objects that are orphaned, meaning they do not have an associated partner unique key ('PartnerUniqueKey'). Depending on the value of the 'MODE' macro variable, it can either simply list the found objects in the log (MODE=REPORT) or permanently delete them (MODE=DELETE). This is a maintenance tool to ensure metadata consistency.
Data Analysis
Type : EXTERNE
The data does not come from SAS tables but is read directly from the SAS metadata server. The connection is established via options such as 'metaserver', 'metaport', etc. The script queries the metadata API to find specific objects.
1 Code Block
DATA STEP
Explanation :
This DATA _NULL_ block is the core of the program. It does not create any output SAS tables. It initializes variables, retains the value of the 'MODE' macro, then constructs a query for metadata. It uses the `metadata_resolve` function to count the number of 'ForeignKey' objects matching the query. If found, it iterates over each object, retrieves its URI, name, ID, and associated table information via `metadata_getnobj`, `metadata_getattr`, and `metadata_getnasn`. Finally, it displays the information in the SAS log and, if the mode is 'DELETE', attempts to delete the object with `metadata_delobj` while checking the operation's return code.
This DATA _NULL_ block is the core of the program. It does not create any output SAS tables. It initializes variables, retains the value of the 'MODE' macro, then constructs a query for metadata. It uses the `metadata_resolve` function to count the number of 'ForeignKey' objects matching the query. If found, it iterates over each object, retrieves its URI, name, ID, and associated table information via `metadata_getnobj`, `metadata_getattr`, and `metadata_getnasn`. Finally, it displays the information in the SAS log and, if the mode is 'DELETE', attempts to delete the object with `metadata_delobj` while checking the operation's return code.
Copied!
| 1 | DATA _null_; |
| 2 | /* Initialize variables. */ |
| 3 | LENGTH FK_id PT_id $17 type FK_uri PT_uri $50 PT_engine $64 PT_name $32 FK_name $60; |
| 4 | call missing ( of _character_ ); |
| 5 | retain mode "&MODE"; |
| 6 | |
| 7 | /* Define a query for ForeignKey FKobjects that do not have a PartnerUniqueKey association */ |
| 8 | FK_obj="omsobj:ForeignKey?ForeignKey[not(PartnerUniqueKey/*)]"; |
| 9 | |
| 10 | /* Determine how many foreign key objects meet the criteria */ |
| 11 | FK_Count=metadata_resolve(FK_obj,type,FK_id); |
| 12 | put "NOTE: Found " FK_Count "ForeignKey objects that do not have an associated PartnerUniqueKey"; |
| 13 | |
| 14 | /* If any were found, iterate through each one to get the attributes and associations */ |
| 15 | IF FK_Count > 0 THEN DO i = 1 to FK_Count; |
| 16 | /* Get the URI for the nth ForeignKey found */ |
| 17 | FK_rc=metadata_getnobj(FK_obj,i,FK_uri); |
| 18 | |
| 19 | /* Get the name and ID of the ForeignKey FKobject */ |
| 20 | FK_rc=metadata_getattr(FK_uri,"Name",FK_name); |
| 21 | FK_rc=metadata_getattr(FK_uri,"Id",FK_id); |
| 22 | |
| 23 | /* get the table name and id this ForeignKey is associated with */ |
| 24 | PT_rc=metadata_getnasn(FK_uri,"Table",1,PT_uri); |
| 25 | IF PT_rc > 0 THEN DO; |
| 26 | /* Get the name and ID of the table. */ |
| 27 | PT_rc=metadata_getattr(PT_uri,"SASTableName",PT_name); |
| 28 | PT_rc=metadata_getattr(PT_uri,"Id",PT_id); |
| 29 | END; |
| 30 | |
| 31 | /* REPORT and DELETE */ |
| 32 | put "NOTE: Found foreign key: " FK_name "with id: " FK_id "for table: " PT_name "with id: " PT_id; |
| 33 | IF mode = "DELETE" THEN DO; |
| 34 | FK_rc = metadata_delobj(FK_uri); |
| 35 | IF FK_rc THEN put 'ERROR: metadata_delobj failed with return code ' FK_rc; |
| 36 | ELSE put 'NOTE: Foreign key successfully deleted'; |
| 37 | END; |
| 38 | END; |
| 39 | RUN; |
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 : Copyright © 2025, SAS Institute Inc., Cary, NC, USA. All Rights Reserved.
* SPDX-License-Identifier: Apache-2.0
Expert Advice
Simon
Expert SAS et fondateur.
« Managing a SAS Metadata repository requires periodic "garbage collection" to prevent performance degradation and synchronization issues. Orphaned Foreign Keys—those missing a corresponding Unique Key—often result from incomplete manual deletions or failed migrations. This script provides a critical administrative safeguard by identifying and pruning these dead links. »