Resolution of field value to its corresponding column

[sanalitics]

Following is my query:

Here is the input dataset "test":

data test;
input name $ val $ AA BB CC;
cards;
A AA 10 33 44
B BB 20 21 23
C CC 30 34 66
;
run;

I would like to get the result in the field "Final" that refers to the field in the value of the variable "VAL". Following is the expected table result

name VAL AA  BB  CC  Final
A       AA    10   33   44  10
B      BB     20   21  23  21
C      CC     30  34  66  66

[sbxkoenk · Accepted solution]

data test;
input name $ val $ AA BB CC;
cards;
A AA 10 33 44
B BB 20 21 23
C CC 30 34 66
;
run;

data want(drop=i);
 set test;
 array mynums{*} AA BB CC;
 do i=1 to dim(mynums);
  if vname(mynums(i))=val then Final=mynums(i);
 end;
run;
/* end of program */

Koen

[sanalitics]

Hello Koen, thank you for the response. Do we have an alternate solution utilizing macro variables ?

[novinosrin]

Hi @sanalitics  + @sbxkoenk  Wouldn't  VVALUEX function be more terser?

data test;
input name $ val $ AA BB CC;
cards;
A AA 10 33 44
B BB 20 21 23
C CC 30 34 66
;
run;


data want;
 set test;
 want=vvaluex(val);
run;

proc print noobs;run;

name	val	AA	BB	CC	want
A	AA	10	33	44	10
B	BB	20	21	23	21
C	CC	30	34	66	66

[Tom]

---

@sanalitics wrote:
Hello Koen, thank you for the response. Do we have an alternate solution utilizing macro variables ?

---

How could a macro variable help?  What code would you use the macro variable to create?  Remember that the code for a data step cannot change once the step is running.

[sanalitics]

Hello Tom

Of course the use of array and vvaluex are ideal solutions.

I was wondering if we could take the distinct of name and val so that we have macro variables created

which has the VAL corresponding to name.

BR

[Tom]

I still don't understand how a macro variable helps.

You could populate a macro variable with the list of variable names and use it to either define the array:

%let varlist=AA BB CC;
...
  array vars &varlist;
...

Or perhaps to check if the value of the field name variable is valid.

if findw("&varlist",varname,' ','it') then value=input(vvaluex(varname),32.);

[FreelanceReinh]

Hello @sanalitics,

---

@sanalitics wrote:
Do we have an alternate solution utilizing macro variables ?

---

You could use a macro variable to abbreviate the variable list in the DATA step code:

%let v=AA,BB,CC;

data want;
set test;
Final=choosen(findw("&v",val,',','et'),&v);
run;

But the solution using VVALUEX is still shorter even if you add the INPUT function to convert the character result to a numeric value.