Using the DATASETS procedure, we can easily modify SAS variable attributes such as name, format, informat and label:
proc datasets library=libref; modify table_name; format var_name date9.; informat var_name mmddyy10.; label var_name = 'New label'; rename var_name = var_new_name; quit;
We cannot, however, modify fixed variable attributes such as variable type and length.
Notice that we apply variable attributes modifications to one table at a time; that table name is specified in the modify statement of the proc datasets. There might be multiple modify statements within a single proc datasets to apply modifications to multiple data tables.
But what if we need to change variable attributes in all tables of a data library where that variable is present? Say, there is a data model change that needs to be applied consistently to all the data tables in that model.
Let’s consider a business case for a SAS programmer working for a pharmaceutical company or a contract research organization (CRO) managing data for a clinical trials project. Imagine that you need to apply some variable attributes modifications to all SDTM (Study Data Tabulation Model) and/or ADaM (Analysis Data Model) datasets to bring them in compliance with the CDISC (Clinical Data Interchange Standards Consortium) requirements. Obviously, “manually” going through dozens or hundreds of data tables in different domains to identify whether or not they contain your variable of interest is not an efficient solution.
Better data management practice
This variable attributes update process can be fully automated using the data-driven SAS macro loops approach as described in my earlier blog post. First, we need to programmatically loop through all the tables in a specified library to find the tables that contain the variable to be modified. Then, for each iteration of that data loop we use SAS macro looping to generate multiple modify statements in proc datasets with subsequent sub-statements (format, informat, label, rename).
SAS provides a multitude of datasets and views in the SASHELP library. While many of them can be used in coding examples, demos, and testing, there are some that provide invaluable information about your SAS environment. Take a look at the views in the SASHELP library whose names start with the letter “V”: Vcolumn, Vlibname, Vmacro, Voption, Vtable, Vtitle, and others.
These SAS views contain a wealth of information about your SAS environment, and reflect any new changes to your SAS environment dynamically. For example, if you run the following code to create a macro variable:
%let aaa = a;
and then open the Vmacro view, you will see that macro variable listed right there at the very top.
For our purpose of looping through all the tables in a library containing a particular variable, we are going to use the Vcolumn view, as it has all the information we need – libname (Library Name), memname (Member Name), name (Column Name):
Having that SASHELP.VCOLUMN table view available we can easily implement our code for modifying variable attributes in all the tables of a data library. Here is the SAS code example:
libname sdtm 'c:\'; data sdtm.a; x=1; label x='Old x-label'; run; data sdtm.b; y=1; label y='Old y-label'; format y mmddyy8. run; data sdtm.c; y=2; label y='Old y-label'; format y date9.; run; %macro change_var_attr ( lib=, /* libref, required parameter */ var=, /* variable name, required parameter */ new_label='_', /* new label, in quotes, optional parameter */ new_format=_, /* new format, optional parameter */ new_informat=_, /* new informat, optional parameter */ new_name=_ /* new variable name, optional parameter */ ); /* get all tables in a library &lib containing variable &var */ data target_tables (keep=memname); set sashelp.vcolumn (keep=libname memname name); where (upcase(libname) eq upcase("&lib")) and (upcase(name) eq upcase("&var")); run; proc datasets library=&lib; %let dsid = %sysfunc(open(target_tables)); %syscall set(dsid); /* no leading ampersand for dsid with %syscall */ %do %while(%sysfunc(fetch(&dsid)) eq 0); modify &memname; %if &new_label ne '_' %then %str(label &var = &new_label;); %if &new_format ne _ %then %str(format &var &new_format;); %if &new_informat ne _ %then %str(informat &var &new_informat;); %if &new_name ne _ %then %str(rename &var = &new_name;); %end; %let rc = %sysfunc(close(&dsid)); run; quit; %mend change_var_attr; %change_var_attr(lib=sdtm, var=y, new_label='New label, commas allowed', new_format=yymmddd10., new_name=z)
The centerpiece of the code is the change_var_attr macro. The macro has two required parameters, lib and var, and four optional parameters, new_label, new_format, new_informat, and new_name.
To get all the tables in a library &lib containing the variable &var, we query and subset the SAS view SASHELP.VCOLUMN and create a target_tables SAS dataset.
Then in PROC DATASETS, we macro-loop through the target_tables and generate as many modify statements as there are observations in the target_tables dataset, one for each table to be modified. Each modify statement is followed by one or several optional statements – label, format, informat, and rename.
%syscall set (dsid); statement invokes a CALL SET routine that automatically sets the value of macro variable memname when an observation with memname variable is fetched (read) from the target_tables dataset.
There may be other programming techniques to solve the problem of modifying variable attributes across all data sets of a data library. You are welcome to suggest your solution in the comments section below.
For those who were patient enough to read to the end… If you need to apply modifications to a variable in tables across multiple data libraries, you can still do it with a single invocation of the change_var_attr macro. In order to do that you would need to define a new data library logically concatenating all the relevant libraries. For example, the following libname statement assigns the libref combo as a logical concatenation of both, sdtm and adam libraries:
libname combo (sdtm adam);