Drop or Keep Variables With Same Suffix
Dropping variables with the same prefix in SAS is easy. Simply use the colon operator along with the common prefix in a drop or keep statement. Consequently, the “drop var: ;” drops all variables with the prefix “var”. But if you want to drop or keep all variables with a common suffix, things get a little more complicated, because the colon syntax is not applicable in this case. The example below shows you how to do this.
First, let us create a small example data set. This data set is for demonstration purposes only.
data testdata; input ID FirstVar SecondVar ThirdVar One Two Three; datalines; 1 1 2 3 1 2 3 2 4 5 6 4 5 6 3 7 8 9 7 8 9 4 1 2 3 1 2 3 5 4 5 6 4 5 6 ;
Next, I define the common suffix for the variables to be dropped. In this case, I set the suffix to var. Then, I use the dictionary.columns table to extract all variable names, that has this suffix and save them in a macro variable separated by spaces. Finally, I write the variables to be dropped to the log. This lets me check that the variables to be dropped are as expected.
%let suffix=var; proc sql noprint; select name into :vars separated by ' ' from dictionary.columns where upcase(libname)=upcase('work') and upcase(memname)=upcase('testdata') and upcase(substr(name,length(name)-(length("&suffix")-1),length("&suffix")))=upcase("&suffix"); quit; %put &vars;
Finally, I create a data set from the initial testdata data set and drop the variables in the created macro variable. I do an initial check that the macro variable &vars is not empty. If it is not empty, I drop the variables in the list.
data want; set testdata; if length("&vars") ne 0 then do; drop &vars; end; run;
The data set want drops the variables with the common suffix “var” as expected.
Dropping or keeping variables with common suffixes is more complicated than in the prefix case. But with the help of dictionary tables, it is not too difficult.
If you are not at all familiar with dictionary tables, I recommend that you consult the SAS Documentation About Dictionary Tables. Also the article Exploring DICTIONARY Tables and Views has a great introduction to the concept of dictionary tables.
You can download the entire code from this example here.