"What's in a name?" asked Juliet. "That which we call a rose by any other name would smell as sweet." What if it's not roses we're after, but variable names which hold the values needed for a calculation?
Here is an example of the data set:
The task was to assign a value to each of the flag variables based on whether or not the year_month value extracted from the flag variable name fell between the values in the start_year_month and stop_year_month variables. Here is a mockup of the desired results:
I resolved this tricky problem of matching values to variable names using an ARRAY or two, my old friend the SCAN function, and the little-known but oh-so-useful VNAME function. I learned about the VNAME function from Ron Cody's book "SAS Functions by Example" and the excellent SAS training course of the same name.
First, we need to know how many flag variables there are so we can properly dimension a temporary array which will hold the values extracted from the variable names. A quick SQL query of the dictionary.columns table will tell us how many variable names begin with 'flag_':
proc sql noprint; select count(*) into :nvars trimmed from dictionary.columns where libname='WORK' and memname='SOURCE' and name like 'flag_%' ; quit; %put NOTE: &=nvars;
The query indicates that there are 6 'flag_' variables in this data set. Next, let's write code to processes the source data set:
data work.results; set work.source; /* Create the VAL array for all flag variable values */ array val[*] flag_:; /* Create temporary NAME array to hold values extracted from flag variable names */ array name[&nvars] $ 7 _temporary_; if _n_=1 then do i=1 to dim(name); /* Extract the 'last word' from the flag variable name */ /* Array elements will each now hold a valid year-month value */ name[i]=strip(scan(vname(val[i]),-1,'_')); put name[i]=; end; do i=1 to dim(val); /* The Boolean expression produces either a 1 or 0 */ /* If the flag variable is in the date range, value is 1 */ /* Otherwise the value is 0 */ val[i]=start_year_month<= name[i] <= stop_year_month; end; drop i; run;
The results are just what the doctor ordered!
You can download the code to play with from this link.
Until next time, may the SAS be with you!