New Contributor
Posts: 4

# Use a value from table A as a variable name in table B

[ Edited ]

Hi!

I wonder if there is a way of using a certain value of a table as a variable name for another table. For example:

I have two tables, table TBF (to be filled) and table INFO.

Table TBF looks like this:

 B C5 C6 P1 P2 P3 B24 16 € 15 € B52 84 € 64 € B62 35 € 86 € B12 12 € 215 € B13 74 € 56 €

And I have to fill P1--P3 given table INFO:

 P B C P1 B24 C5 P2 B52 C5 P3 B62 C5 P1 B12 C6 P2 B13 C6

So for example, when B = B24, P1 = C5, updating the table TBF as:

 B C5 C6 P1 P2 P3 B24 16 € 15 € 16€ B52 84 € 64 € B62 35 € 86 € B12 12 € 215 € B13 74 € 56 €

So I need something like:

```%LET LP = P1;
TBF.&LP = (select INFO.C
where TBF.B = INFO.B and INFO.P = &LP
from INFO, TBF)```

But doing so, when B=B24, P1 will write "C5" instead of 16€.

How can I get SAS to keep "C5" as a variable name instead of a value?

The table TBF finally should be:

 B C5 C6 P1 P2 P3 B24 16 € 15 € 16€ . . B52 84 € 64 € . 84€ . B62 35 € 86 € . . 35€ B12 12 € 215 € 215€ . . B13 74 € 56 € . 56€ .

If anything is confusing I'll be glad to clarify it!

Thank you very much,

Super User
Posts: 9,840

## Re: Use a value from table A as a variable name in table B

Post test data in the form of a datastep in a code window (its the {i} above post are).  As such I am not typing that test data in nor guessing structure, so this is just a try:

```data _null_;
set info end=last;
if _n_=1 then call execute('data want; set tbf;');
call execute(cat('if b="',strip(b),'" then ',strip(p),'=',strip(c),';'));
if last the call execute(';run;');
run;```
PROC Star
Posts: 629

## Re: Use a value from table A as a variable name in table B

Hi,

Check out this.

``````data tbf;
infile datalines missover;
input B \$ C5 \$ C6 \$;
datalines;
B24 16€ 15€
B52 84€ 64€
B62 35€ 86€
B12 12€ 215€
B13 74€ 56€
;
run;
proc sql;
create table mod as
select b,C5 as val,"C5" as C
from tbf
union
select b,C6 as Val,"C6" as C
from tbf;
quit;

data info;
input P \$ B \$ C \$;
cards;
P1 B24 C5
P2 B52 C5
P3 B62 C5
P1 B12 C6
P2 B13 C6
;
run;

PROC SQL;
create table upd as
select a.P,a.B,b.val
from info a
left join mod b on (a.c=b.c and a.b=b.b);
quit;
proc sort data=upd;
by b;
PROC TRANSPOSE DATA=upd out=mer(drop=_name_);
by b;
id p;
var val;
run;
proc sort data=mer;
by b;
proc sort data=tbf;
by b;
data want;
merge tbf mer;
by b;
run;``````
Thanks,
Suryakiran
Super User
Posts: 10,570

## Re: Use a value from table A as a variable name in table B

First transpose to long, then merge with info, then transpose back, then merge with original:

``````data tbf;
input B \$ C5 C6;
n = _n_;
cards;
B24 16 15
B52 84 64
B62 35 86
B12 12 215
B13 74 56
;
run;

data info;
input P \$ B \$ C \$;
cards;
P1 B24 C5
P2 B52 C5
P3 B62 C5
P1 B12 C6
P2 B13 C6
;
run;

proc sort data=tbf;
by B;
run;

proc transpose data=tbf out=int (rename=(_name_=C));
by B;
var c5 c6;
run;

proc sort data=info;
by B C;
run;

data int2;
merge
int (in=in_int)
info (in=in_info)
;
by B C;
put b \$hex16. " " c \$hex16." " in_info;
if in_int;
run;

proc transpose data=int2 (where=(P ne " ")) out=want1 (keep=b p:);
by b;
id p;
var col1;
run;

data want;
merge
tbf
want1
;
by b;
run;

proc sort data=want;
by n;
run;``````

You can omit variable n if the original order does not need to be preserved.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 2,061

## Re: Use a value from table A as a variable name in table B

Some more fun ways of solving:-

data tbf;

input B \$ C5 C6;

cards;

B24 16 15

B52 84 64

B62 35 86

B12 12 215

B13 74 56

;

run;

data info;

input P \$ B \$ C \$;

cards;

P1 B24 C5

P2 B52 C5

P3 B62 C5

P1 B12 C6

P2 B13 C6

;

run;

data want;

keep B C5 C6 p:;

drop p;

if _N_ = 1 then do;

if 0 then set info;

declare hash h(dataset:'info');

h.defineKey('B');

h.defineData('p','c');

h.defineDone();

end;

set tbf;

array _p(*) P1-P3;

array _c(*) c5-c6;

rc=h.find();

if rc=0 then

do;

do i=1 to dim(_p);

do j=1 to dim(_c);

if vname(_p(i))=p and vname(_c(j))=c then

do;

_p(i)=_c(j);

leave;

end;

else continue;

end;

end;

end;

run;

Posts: 1,392

## Re: Use a value from table A as a variable name in table B

This is a good situation for taking the data in INFO to write out SAS statements for a later %include:

``````data tbf;
infile datalines missover;
input B \$ C5 \$ C6 \$;
datalines;
B24 16€ 15€
B52 84€ 64€
B62 35€ 86€
B12 12€ 215€
B13 74€ 56€
;

data info;
input P \$ B \$ C \$;
cards;
P1 B24 C5
P2 B52 C5
P3 B62 C5
P1 B12 C6
P2 B13 C6
;
run;

filename tmp temp;
data _null_;
set info end=end_of_info;
file tmp;
put 'IF B=' B quote. ' then ' P '=' C ';'  @;
if end_of_info=0 then put 'ELSE';
run;

options source2;
data want;
set tbf;
%include tmp;
run;
``````
Super User
Posts: 10,849

## Re: Use a value from table A as a variable name in table B

Very interesting question.

``````data tbf(index=(b));
input B \$ C5 C6;
cards;
B24 16 15
B52 84 64
B62 35 86
B12 12 215
B13 74 56
;
run;

data info(index=(b));
input P \$ B \$ C \$;
cards;
P1 B24 C5
P2 B52 C5
P3 B62 C5
P1 B12 C6
P2 B13 C6
;
run;
proc sql noprint;
select p into : vars separated by ' '
from (select distinct p from info)
order by input(compress(p,,'kd'),best32.);
quit;
%put &vars ;
data want;
merge tbf info;
by b;
array x{*} &vars ;
do i=1 to dim(x);
if upcase(vname(x{i}))=upcase(p) then do;x{i}=vvaluex(c);leave;end;
end;
drop i p c;
run;``````
Discussion stats
• 6 replies
• 225 views
• 2 likes
• 7 in conversation