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

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

Florent — Tue, 01 Mar 2011 19:59:26 GMT

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

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

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

Tim_SAS — Tue, 01 Mar 2011 20:14:01 GMT

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]

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

Florent — Tue, 01 Mar 2011 20:34:36 GMT

Hi Tim,

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

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

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

Tim_SAS — Tue, 01 Mar 2011 20:46:38 GMT

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.

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

Cynthia_sas — Tue, 01 Mar 2011 20:59:57 GMT

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]

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

Ksharp — Wed, 02 Mar 2011 05:11:09 GMT

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

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

Florent — Wed, 02 Mar 2011 11:32:25 GMT

@ 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

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

data_null__ — Wed, 02 Mar 2011 12:44:29 GMT

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]

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

Ksharp — Thu, 03 Mar 2011 02:17:40 GMT

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