topic Re: Resolution of field value to its corresponding column in SAS Programming

Resolution of field value to its corresponding column

sanalitics — Sat, 14 Aug 2021 20:57:10 GMT

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

Re: Resolution of field value to its corresponding column

sbxkoenk — Sat, 14 Aug 2021 21:03:24 GMT

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

Re: Resolution of field value to its corresponding column

sanalitics — Sat, 14 Aug 2021 21:11:21 GMT

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

Re: Resolution of field value to its corresponding column

novinosrin — Sat, 14 Aug 2021 21:21:42 GMT

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

Re: Resolution of field value to its corresponding column

Tom — Sat, 14 Aug 2021 21:25:38 GMT

@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.

Re: Resolution of field value to its corresponding column

FreelanceReinh — Sat, 14 Aug 2021 21:56:01 GMT

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.

Re: Resolution of field value to its corresponding column

sanalitics — Sun, 15 Aug 2021 06:18:58 GMT

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.

Re: Resolution of field value to its corresponding column

Tom — Sun, 15 Aug 2021 15:00:43 GMT

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.);