DATA Step, Macro, Functions and more

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

Reply
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:

 

BC5C6P1P2P3
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:

 

PBC
P1B24C5
P2B52C5
P3B62C5
P1B12C6
P2B13C6

 

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

 

BC5C6P1P2P3
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:

 

BC5C6P1P2P3
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
Super User
Posts: 9,227

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

Posted in reply to DaniLagetsson

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;
Super Contributor
Posts: 478

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: 9,611

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

Posted in reply to DaniLagetsson

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
PROC Star
Posts: 1,357

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

Posted in reply to DaniLagetsson

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;

 

Trusted Advisor
Posts: 1,294

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

Posted in reply to DaniLagetsson

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,623

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

Posted in reply to DaniLagetsson

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;
Ask a Question
Discussion stats
  • 6 replies
  • 203 views
  • 2 likes
  • 7 in conversation