topic Re: Get column value from column name as string in SAS Programming

Get column value from column name as string

Edoedoedo — Thu, 16 Apr 2015 20:26:21 GMT

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:

CHANNEL	A	B	C	VALUE
B	1	2	3	2
A	4	5	6	4
A	7	8	9	7
C	10	11	12	12

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

Re: Get column value from column name as string

ballardw — Thu, 16 Apr 2015 20:50:59 GMT

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;

Re: Get column value from column name as string

Haikuo — Thu, 16 Apr 2015 21:04:50 GMT

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.

Re: Get column value from column name as string

Ron_MacroMaven — Thu, 16 Apr 2015 21:49:22 GMT

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 ;

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

Re: Get column value from column name as string

art297 — Thu, 16 Apr 2015 23:34:15 GMT

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

Re: Get column value from column name as string

Reeza — Thu, 16 Apr 2015 23:43:42 GMT

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;

Re: Get column value from column name as string

Haikuo — Fri, 17 Apr 2015 00:11:52 GMT

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;

Re: Get column value from column name as string

Haikuo — Fri, 17 Apr 2015 00:16:36 GMT

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

Re: Get column value from column name as string

Reeza — Fri, 17 Apr 2015 00:33:55 GMT

Yay! What do I win ?

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

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

Re: Get column value from column name as string

art297 — Fri, 17 Apr 2015 00:50:56 GMT

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.

Re: Get column value from column name as string

Haikuo — Fri, 17 Apr 2015 01:05:29 GMT

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.

Re: Get column value from column name as string

Edoedoedo — Fri, 17 Apr 2015 07:54:33 GMT

Thank you guys,

it is amazing how many alternatives there exists to this problem

I think Reeza's solution is the most elegant, but thank you all for your ideas, I learned a lot.

Greetings

Re: Get column value from column name as string

Tom — Fri, 17 Apr 2015 11:50:18 GMT

You probably will need to add an INPUT() function to convert the character string returned by VVALUEX() function back to a number.

Also there is risk of rounding of the value caused by the number/character/number conversion.

value=input(vvaluex(channel),32.);

Re: Get column value from column name as string

Ksharp — Fri, 17 Apr 2015 13:42:55 GMT

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

Re: Get column value from column name as string

Haikuo — Fri, 17 Apr 2015 13:57:44 GMT

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

Re: Get column value from column name as string

art297 — Fri, 17 Apr 2015 15:28:05 GMT

: 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=all:);

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;

Re: Get column value from column name as string

Tom — Fri, 17 Apr 2015 15:37:25 GMT

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.

Re: Get column value from column name as string

Haikuo — Fri, 17 Apr 2015 15:47:12 GMT

, are you sure it worked? It seems to me it can't.

array all(&&nmobs.) $;

The time point when this macro variable is supposed to be resolved, it does not exist yet. It only exists after SAS code " call symputx('nmobs',numobs*4);" being executed, which is way late for its call for being resolved.

You may try to clean up your existing macro variables and give another shot.

Haikuo

Re: Get column value from column name as string

art297 — Fri, 17 Apr 2015 15:54:07 GMT

and : You're both obviously correct! Whatever I had done worked before I posted the message, but I can't replicate it. The code fails miserably. Darn!

I must have somehow created the macro variable while I was testing various iterations of the code.

Re: Get column value from column name as string

SAS_inquisitive — Fri, 14 Jul 2017 22:50:17 GMT

@Reeza, can you help me get this? According to SAS documentation, vvaluex takes expression as argument. But here you have variable 'channel' as argument. I think vvalue only takes variable as argument. Thanks !