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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.