"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!
Mark
8 Comments
Mark,
Wow; what a very nice--and nicely written--trick. You are truly a SAS Jedi!
----MMMMIIIIKKKKEEEE
Woot! Thanks, Mike! High praise from the guy who wrote the book on hacking SAS :-)
With use of wildcards in an array declaration and extracting the "part" you are interested of, you could solve this "problem" with close to one row of effective SAS-code:
data results;
set source;
array flag_ flag_:;
do over flag_;
flag_= (start_year_month <= compress(vname(flag_),'_', 'A') <= stop_year_month);
end;
run;
Perhaps that this solution is not as easy to understand but still shows the opportunities available in SAS. Thanks for the post!
Sometimes when teaching, we make a solution a little more verbose so it's easier to understand. But this is an excellent reduction. You are truly a code poet, amigo!
To 'V' or not to 'V' ;-)
A nice highlight on how versatile the SAS language and the V functions are! Thanks for sharing.
That is the question! :-)
I love the V functions and you are right, they are very underutilized and underappreciated! Thanks for posting a useful example.
So glad to hear that I am not the only SAS function connoisseur :-) Stay SASy, my friend!