DATA Step, Macro, Functions and more

Get column value from column name as string

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Get column value from column name as string

Hi,

I have a dataset where fields are: CHANNEL | A | B | C

- CHANNEL is a character field which cointains 'A', 'B', or 'C'

- A, B, C are numeric fileds which contain some numbers

I'd like to create in a datastep a new column VALUE, with the following logic: if CHANNEL = 'A' then VALUE = A, if CHANNEL = 'B' THEN VALUE = B, etc.

In other words, VALUE must contain the respective value of the column indicated in field CHANNEL for each row.

An example:

CHANNELABCVALUE
B1232
A4564
A7897
C10111212

In real world CHANNELs are hundreds, so I can't add hundreds of if in the datastep, I'd like to write a more elegant solution in a few rows of code.

Thanks a lot.

Greetings


Accepted Solutions
Solution
‎04-16-2015 07:43 PM
Super User
Posts: 17,819

Re: Get column value from column name as string

Use the VVALUEX function


data have;

     input (CHANNEL A    B    C) (:$8.);

     cards;

B    1    2    3   

A    4    5    6   

A    7    8    9   

C    10   11   12  

;

run;

data want;

     set have;

   value=vvaluex(channel);

run;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Get column value from column name as string

Are you saying that you have hundreds of variables A B C or some non-sequentially named list of a hundred variables that you need to apply?

This works for you example but HUNDREDS of values for Channel are going to get a bit rough.

data work.junk;
input CHANNEL $ A  B C;
array ChannelKeys {3} $ _temporary_ ("A" "B" "C"); /* the values of CHANNEL you are searching for go here*/
array vars A B C; /* list of variables with values to assign go here*/
value = vars[whichc(channel, of ChannelKeys

  • )];
  • datalines;
    B 1 2 3 
    A 4 5 6 
    A 7 8 9 
    C 10 11 12
    ;
    run;

    Regular Contributor
    Posts: 198

    Re: Get column value from column name as string

    thank you BallardW for the test data.

    This is a lookup or mapping problem.

    I am sure there are issues with the case of the value of Channel

    and the list of variables in the array.

    I figured this out with the help of Knuth, who has several programs about sorting and searching.

    Category:Knuth - sasCommunity

    data work.junk;

    input CHANNEL $ A  B C;

    array vars A B C; /* list of variables with values to assign go here*/

    do i = 1 to dim(vars);

       if Channel eq vname(vars(i)) then do;

          value = vars(i);

          leave;

          end ;

       end ;

    datalines;

    B 1 2 3

    A 4 5 6

    A 7 8 9

    C 10 11 12

    ;

    run;

    proc print data = &syslast;

    Ron Fehd  Donald E Knuth maven

    Respected Advisor
    Posts: 3,124

    Re: Get column value from column name as string

    I have written one solution before using Hash, but here is another one using Revolve Function, based on your simplified scenario:

    data have;

         input (CHANNEL A    B    C) (:$8.);

         cards;

    B    1    2    3   

    A    4    5    6   

    A    7    8    9   

    C    10   11   12  

    ;

    data want;

         set have;

         array var channel--c;

         do over var;

               call symputx(vname(var),var);

         end;

         value=resolve('&&&channel');

    run;

    It can be tweaked to adapt to your numeric variables if you have any.

    PROC Star
    Posts: 7,363

    Re: Get column value from column name as string

    : Please explain, step-by-step, how your solution works. I, for one, would be very interested in learning about your technique.

    Respected Advisor
    Posts: 3,124

    Re: Get column value from column name as string

    Art, I think we have a winner: Reeza!

    As for my code:

    data want;

    set have;

    array var channel--c; /*This is to assume all of the variable are char, and fit them all in an array*/

    do over var;

               call symputx(vname(var),var);/*This is to assign the value to the same-name macro variable*/

    end;

    value=resolve('&&&channel');

          /*Not like Symget(), Resolve () will resolve to the bottom of a macro variable for instance of first row:

          &&&channel -> &B -> 2*/

    run;

    Solution
    ‎04-16-2015 07:43 PM
    Super User
    Posts: 17,819

    Re: Get column value from column name as string

    Use the VVALUEX function


    data have;

         input (CHANNEL A    B    C) (:$8.);

         cards;

    B    1    2    3   

    A    4    5    6   

    A    7    8    9   

    C    10   11   12  

    ;

    run;

    data want;

         set have;

       value=vvaluex(channel);

    run;

    Respected Advisor
    Posts: 3,124

    Re: Get column value from column name as string

    SAS does have an incredible deep bench for functions! Well done, Reeza!

    Super User
    Posts: 17,819

    Re: Get column value from column name as string

    Yay! What do I win Smiley Wink?

    I actually presented a talk on avoiding macro's on Wednesday at our user group meeting - this was one of my examples, so fresh in my head. 

    *Presented as in wrote, unfortunately not able to actually attend and present Smiley Sad

    I don't think I've ever seen the call symput and resolve used that way before, its a good solution too Smiley Happy

    PROC Star
    Posts: 7,363

    Re: Get column value from column name as string

    Ouch! Where's my styptic pencil? I think I've just been cut with Occam's Razer!

    Minimally, you win both Haikuo's and my gratitude!

    While your solution is clearly the best for this problem, I'm still interested in finding out more about Haikuo's approach.

    I've always learned that macro variables CAN'T be used in the data step in which they are created.

    Respected Advisor
    Posts: 3,124

    Re: Get column value from column name as string

    Quote from Art :"I've always learned that macro variables CAN'T be used in the data step in which they are created."

    True in general. Most of Macro elements will first be executed/resolved before go to the SAS compiler, unless you tell SAS not to. In above example,

    value=resolve('&&&channel'); used SINGLE quote to skip the Macro facility and directly enter the SAS execution, and only resolved when that statement is hit.

    Super User
    Posts: 9,676

    Re: Get column value from column name as string

    Arthur.T ,

    You can do it also. But I have to point out that way is NOT efficient ,including Bian HaiKuo's code .

    data want;

         set have;

         array var channel--c;

         do over var;

               call symputx(vname(var),var);

         end;

         value=symgetn(CHANNEL);

    run;

    Xia Keshan

    Respected Advisor
    Posts: 3,124

    Re: Get column value from column name as string

    Given Reeza's solution, will call for his Occam's Razer to cut your approach as well Smiley Happy.

    PROC Star
    Posts: 7,363

    Re: Get column value from column name as string

    : I will try to be careful shaving the code but, seriously, I have never thought of these possibilities and usually throw in a CALL EXECUTE when I've needed to utilize variable values and couldn't do it directly.

    The following is a rather useless example that simply transposes a file into one long record. I've always wanted a way to pass a value into an array declaration and the following method appears to work. This definitely gives me something extra to think about over the weekend:

    data want (keep=allSmiley Happy;

      set have nobs=numobs end=last;

      array vars channel--c;

      if _n_ eq 1 then do;

        i=0;

        call symputx('nmobs',numobs*4);

        array all(&&nmobs.) $;

        retain all:;

      end;

      do over vars;

        i+1;

        all(i)=catt(vars);

      end;

      if last then output;

    run;

    Super User
    Super User
    Posts: 6,500

    Re: Get column value from column name as string

    Art -

      That is not going to work.

      The value of the macro variable used in the ARRAY ALL statement is the value from before the data step starts executing.

      Try adding %SYMDEL NMOBS ; statement before the DATA step and check the log for error messages.

    ☑ This topic is SOLVED.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 21 replies
    • 2630 views
    • 11 likes
    • 9 in conversation