DATA Step, Macro, Functions and more

Fill in the value of a new variable based on the content of another one ?

Reply
Frequent Contributor
Posts: 127

Fill in the value of a new variable based on the content of another one ?

Dear experts,

I would like to be able to create a new variable in a dataset based on the content of another one. Let's say I have a dataset 'Test' which contains three variables (i.e. A, B and C) containing numeric values. A fourth variable (i.e. D) contains, as character value, the name of one of the three other variables (thus it can contain 'A', 'B' or 'C'). I would like being able to create a fifth variable (i.e. E) containing the value of the variable refered by the variable D... knowing that the variable referenced in column D could be different for each single observation of my dataset...

By executing the following piece of code and having a look at the content of the dataset, you should better understand what I'm trying to do... Smiley Happy

data test(drop=i);
do i=1 to 3;
a= 1 * i;
b= 2 * i;
c= 3 * i;

if i = 1 then d= 'a'; else
if i = 2 then d= 'c'; else
if i = 3 then d= 'b';

output;
end;
run;


I first thought to implement this by mean of the CALL EXECUTE but I would also like to know if you could come with another (and possibly more efficient) solution.

Thank you in advance for your help !

Regards,
Florent Subject edited

Message was edited by: Florent
Super Contributor
Posts: 394

Re: Fill in the value of a new variable based on the content of another one ?

Check out the VVALUEX function.

[pre]
VVALUEX accepts a character expression as an argument. The function then
evaluates the expression to determine the variable name and returns the value that
is associated with that variable name.[/pre]
Frequent Contributor
Posts: 127

Re: Fill in the value of a new variable based on the content of another one ?

Hi Tim,

Thank you for the very quick answer: it's exactly what I was looking for ! Smiley Happy

Do you know whether there is a similar function available for SAS 8 ? My application is executing remotely on SAS 9.2 (so, no problem) but if the server is down or the connection can not be established then it's running on the users' local session (where SAS 8 - release 8.02 is installed...).

Thanks again for your help !

Regards,
Florent
Super Contributor
Posts: 394

Re: Fill in the value of a new variable based on the content of another one ?

The SAS 8.2 doc is also online here. The VVALUEX function is not included in the list of functions.

Also, this 2004 SUGI paper says that VVALUEX was new for SAS 9.

Sorry.
Super User
Posts: 10,028

Re: Fill in the value of a new variable based on the content of another one ?

Hi.
How about this:


[pre]

data start;
infile datalines;
input a b c d $;
return;
datalines;
1 2 3 a
2 4 6 c
3 6 9 b
;
run;

data make_E;
set start;
length e $ 10;
e=d;
e = tranwrd(e,'a',put(a,best4.));
e = tranwrd(e,'b',put(b,best4.));
e = tranwrd(e,'c',put(c,best4.));
run;
[/pre]




Ksharp
SAS Super FREQ
Posts: 8,866

Re: Fill in the value of a new variable based on the content of another one ?

Hi:
It seems like this is a job for VVALUEX???? Assuming you have A, B, C, D already in existence.

cynthia
[pre]
data start;
infile datalines;
input a b c d $;
return;
datalines;
1 2 3 a
2 4 6 c
3 6 9 b
;
run;

data make_E;
set start;
e = vvaluex(d);
run;

ods listing;
proc print data=make_E;
run;
[/pre]
Frequent Contributor
Posts: 127

Re: Fill in the value of a new variable based on the content of another one ?

@ Cynthia: This is indeed something for VVALUEX function but unfortunately this function was not available on SAS 8 (as mentioned by Tim).

@Ksharp: your code does generate the expected result but requires that we update the code everytime we have a new value coming in (e.g. we add variable F and variable can reference to this new variable).

Again, thanks for your contributions !

Regards,
Florent
Respected Advisor
Posts: 3,799

Re: Fill in the value of a new variable based on the content of another one ?

I think these functions are available in version 8. Does your example data fully represent the problem?

[pre]
data need;
set test;
array num
  • _numeric_;
    if VINARRAYX(d) then do;
    do j = 1 to dim(num) until(vnameX(d) eq vname(num)); end;
    if j le dim(num) then d0 = num;
    end;
    run;
    proc print;
    run;
    [/pre]

    [pre]
    Obs a b c d j d0

    1 1 2 3 a 1 1
    2 2 4 6 c 3 6
    3 3 6 9 b 2 6
    [/pre]
  • Super User
    Posts: 10,028

    Re: Fill in the value of a new variable based on the content of another one ?

    I am not sure whether the following code can work for SAS8, I have not used it before.


    [pre]
    data start;
    infile datalines;
    input a b c d $;
    return;
    datalines;
    1 2 3 a
    2 4 6 c
    3 6 9 b
    ;
    run;
    data start;
    set start;
    array var{*} a--c ; *a is first variable, b is last variable;
    do i=1 to dim(var);
    if vname(var{i}) eq d then e=var{i};
    end;
    run;

    [/pre]



    Ksharp
    Ask a Question
    Discussion stats
    • 8 replies
    • 927 views
    • 0 likes
    • 5 in conversation