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,
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;
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;
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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.