Jedi SAS Tricks - Variable Names to Values with VNAME

8

"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:

listing of the original source data

Original Data

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:

Listing of the desired output data

Desired Output

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!

Listing of the results data set

Results

You can download the code to play with from this link.

Until next time, may the SAS be with you!
Mark

Tags
Share

About Author

SAS Jedi

Principal Technical Training Consultant

Mark Jordan (a.k.a. SAS Jedi) grew up in northeast Brazil as the son of Baptist missionaries. He served 20 years as a US Navy submariner, pursuing his passion for programming as a hobby. Upon retiring from the Navy in 1994, he turned his hobby into a dream job and has been a SAS programmer ever since. Mark writes and teaches a broad spectrum of SAS Foundation programming classes, and is proud to announce his first book, "Mastering the SAS® DS2 Procedure: Advanced Data Wrangling Techniques". When he isn’t writing, teaching, or posting “Jedi SAS Tricks” here on the SAS Learning Post, Mark enjoys playing with his grand and great-grandchildren, hanging out at the beach and reading science fiction novels. His secret obsession is flying toys – kites, rockets, drones – and though he usually tries to convince Lori that they are for the grandkids, she isn't buying it. Mark lives in Toano, VA with his wife, Lori, and their cat, the amazing Tiger Man. To connect with Mark, check out his SAS Press Author page, follow him on Twitter @SASJedi or connect on Facebook or LinkedIn.

8 Comments

  1. 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!

    • SAS Jedi

      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!

  2. I love the V functions and you are right, they are very underutilized and underappreciated! Thanks for posting a useful example.

Leave A Reply

Back to Top