BookmarkSubscribeRSS Feed
DaniLagetsson
Calcite | Level 5

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,

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
SuryaKiran
Meteorite | Level 14

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
Kurt_Bremser
Super User

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.

novinosrin
Tourmaline | Level 20

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;

 

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1041 views
  • 2 likes
  • 7 in conversation