Last week, I blogged about Dynamic Programming in SAS with CALL EXECUTE. The week before, I blogged about how to Read SAS Metadata with Dictionary Tables and SASHELP Views. Today I combine the two techniques. In this post, I will demonstrate three examples of how to dynamically change the attributes of one or more data sets in SAS.
Apply format to all date variables
In the first example, I will demonstrate how to apply a format to all date variables in a SAS data set. Take a look at the data below. Luckily, all the date variables start with the word date. This enables us to use the Wildcard operator (:) and apply the date9 format directly with Proc Datasets. Naturally, this would not be possible if the variables did not have a common prefix.
You can see from the Proc Contents output to the right, that the format was applied to the date variables.
data test1; input date1-date3 var1-var3; datalines; 1 1 1 1 2 3 ; proc datasets lib=work nolist; modify test1; format date: date9.; run;quit; proc contents data=test1; run;
Change names for variables with 2018 in the variable name
Next, we make things more complicated. Here, we change the names of all variables that have 2018 in the variable name. I can not solve this problem with a simple operator as above. Instead, I use the Sashelp.Vcolumn table and apply Call Execute logic for each variable that has the string 2018 in the variable name. Furthermore, I apply Call Execute logic in the first and last iteration of the data step. I do this to initialize and end the Proc Datasets. Run the Proc Contents below to verify that the code did indeed change the variable names.
data test2; input one2018 $ two2018 $ three2018 $ var1-var3; datalines; a b c 1 2 3 ; data _null_; set sashelp.vcolumn end=lr; where libname='WORK' and memname='TEST2'; if _n_ = 1 then call execute('proc datasets lib=work nolist; modify test2;'); if find(name, '2018') ne 0 then call execute(compbl(cat('rename ', name, '=', tranwrd(name, '2018', '2019'), ';'))); if lr then call execute('quit;'); run; proc contents data=test2; run;
Change data set names for all data sets with 2018 in the data set name
Finally, we zoom out even further and focus on data set names. Here, we change data set names that contain 2018. Like above, I change the names to contain 2019 instead. I create six data sets. The values and variables in the data sets does not matter. Three of the SAS data sets has the string 2018 in the name. Here, I update the value to 2019 instead. Basically, I use the same logic as above with respect to metadata. However, I use the Change Statement in Proc Datasets instead of the Modify Statement above. Check the Work Library and verify that the data set names has in fact changed.
data one; a=1;run; data two; b=1;run; data three; c=1;run; data one2018; d=1;run; data two2018; e=1;run; data three2018;f=1;run; data _null_; set sashelp.vcolumn end=lr; where libname='WORK' and find(memname, '2018') ne 0; if _n_ = 1 then call execute('proc datasets lib=work nolist;'); call execute(compbl(cat('change ', memname, '=', tranwrd(memname, '2018', '2019'), ';'))); if lr then call execute('quit;'); run;
In this post, I have demonstrated three examples of how you can use SAS metadata and Call Execute logic to dynamically alter data set attributes in SAS. It is not hard to see that the techniques I use in this post can be applied to other similar problems as well. I let the above code stand as templates for you to use on similar problems that can be solved in the same way.
See the related post Understand the Dosubl Function in SAS. Also, if you want to learn more about Proc Datasets, read the article PROC DATASETS: The Swiss Army Knife of SAS Procedures by the great Michael A. Raithel.
You can download the entire code from this post here.