The VVALUEX function allows fetching the value of a variable determined at run-time, by passing as argument another variable, for each observation.
For example, the following statement in a datstep:
NEW_FIELD = vvaluex(DYN_FIELD);
Would produce the following result:
DYN_FIELD | A | B | C | NEW_FIELD |
A | 1 | 2 | 3 | 1 |
A | 5 | 6 | 7 | 5 |
B | 9 | 10 | 11 | 10 |
C | 13 | 14 | 15 | 15 |
I would like to perform the inverse: set a value of a variable determined at run-time, ideally, a function such as this, callable from within a datastep:
call SETVALUEX(DYN_FIELD, SOURCE_FIELD);
DYN_FIELD | A | B | C | SOURCE_FIELD |
A | 1 | . | . | 1 |
A | 2 | . | . | 2 |
B | . | 3 | . | 3 |
C | . | . | 4 | 4 |
Why not just use an array?
%let varlist=A B C ;
%let n=%sysfunc(countw(&varlist));
%let namelist="%sysfunc(tranwrd(&varlist,%str( )," "))";
data want ;
set have ;
array vars &varlist ;
array varnames (&n) $32 _temporary_ (&namelist);
vars(whichc(dyn_field,of varnames(*))=source_field;
run;
You could generate the needed macro variables from the data.
proc sql noprint;
select distinct dyn_field,quote(trim(dyn_field))
into :varlist separated by ' '
, :varnames separated by ' '
from have
;
%let n=&sqlobs;
quit;
Why not just use an array?
%let varlist=A B C ;
%let n=%sysfunc(countw(&varlist));
%let namelist="%sysfunc(tranwrd(&varlist,%str( )," "))";
data want ;
set have ;
array vars &varlist ;
array varnames (&n) $32 _temporary_ (&namelist);
vars(whichc(dyn_field,of varnames(*))=source_field;
run;
You could generate the needed macro variables from the data.
proc sql noprint;
select distinct dyn_field,quote(trim(dyn_field))
into :varlist separated by ' '
, :varnames separated by ' '
from have
;
%let n=&sqlobs;
quit;
I had never came across these functions before. they are really interesting thanks.
I managed to get the desired result by adding the VVALUE function :
data HAVE;
dyn_field='A';
A = 1;
B=.;
C=.;
OUTPUT;
dyn_field='A';
A = 2;
B=.;
C=.;
OUTPUT;
dyn_field='B';
A = .;
B=3;
C=.;
OUTPUT;
dyn_field='C';
A = .;
B=.;
C=4;
OUTPUT;
RUN;
DATA WANT;
SET HAVE;
SOURCE_FIELD=STRIP(VVALUEX(STRIP(VVALUE(DYN_FIELD))));
RUN;
SAS/IML has such function, check CALL SETVAL() .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.