topic Re: Use a value from table A as a variable name in table B in SAS Programming

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

DaniLagetsson — Fri, 01 Dec 2017 12:30:04 GMT

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,

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

RW9 — Fri, 01 Dec 2017 12:22:09 GMT

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;

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

Kurt_Bremser — Fri, 01 Dec 2017 12:34:13 GMT

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.

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

novinosrin — Fri, 01 Dec 2017 19:57:45 GMT

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;

run;

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

SuryaKiran — Fri, 01 Dec 2017 21:07:37 GMT

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;

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

mkeintz — Fri, 01 Dec 2017 23:21:22 GMT

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;

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

Ksharp — Sat, 02 Dec 2017 11:04:02 GMT

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;