How to create a table compsed out of 2 translating tables

[J111]

What is the best way to create table C_Want from A_Have and B_Have ?

ALl tables are long - this is only an example.

Data A_Have ;
INPUT NUM_A $CHAR2. Ser $CHAR20. ;
cards ;
A2 B$1
A3 B$2
A4 B$3
A5 B$4
;
Run ;

Data B_Have ;
INPUT NUM_B $CHAR2. Formula $CHAR20. ;
cards ;
B2 A2 / A3
B3 A4 / A5
;
Run ;

Data C_Want ;
INPUT NUM_B $CHAR2. Formula $CHAR20. ;
cards ;
B2 B$1 / B$2
B3 B$3 / B$4
;
Run ;

[jklaverstijn]

If there is some logic behind these values (that smell of Excel formula's 😉 )? I would rather implement that logic instead of going through many predictable rows in tables.

That said, If there is not, I would code a data step that reads B_Have, loads A_Have in a hash table and perform lookups on that hash table that would map A2 to B$1 etc. I can provide a code example but would have to know first what you mean by "long"? Thousands of rows, milions, ...?

Hope this helps,

- Jan

[J111]

Hello,

"Long" meaning hundreds and not only 2 obs.

A code would be wonderful. Thanks

[J111]

There is no logic

Just general sequence of 2 variables:-

A(I) and B(J), For  example

I = 1,2,3...  and J = 7,8,9...

B$(K) For any K  For example K= 11108

Thanks !

[Ksharp]

Data A_Have ;
INPUT NUM_A $CHAR2. Ser $CHAR20. ;
cards ;
A2 B$1
A3 B$2
A4 B$3
A5 B$4
;
Run ;


Data B_Have ;
INPUT NUM_B $CHAR2. Formula $CHAR20. ;
cards ;
B2 A2 / A3
B3 A4 / A5
;
Run ;

data format;
 set a_have(rename=(num_a=start ser=label));
 retain fmtname 'fmt' type 'c';
run;
proc format cntlin=format;
run;
data want;
 set b_have(rename=(Formula=_f));
 length formula $ 40;
 do i=1 to countw(_f,'/ '); 
  formula=catx(' / ',formula,put(scan(_f,i,'/ '),$fmt.));
 end;
 drop i _f;
run;

[J111]

Thanks - One more detail to solve:- the formula could have more seperators  such as (+ * / -)

Data B_Have ;
INPUT NUM_B $CHAR2. Formula $CHAR30. ;
cards ;
B2 A2 / (A3 + A4) * 2
B3 A4 / (A5 - A2)
;
Run ;

[Tom]

The ask does not make any sense.

What the heck is B$1 and B$2 supposed to be?

Is that supposed to look like Excel notation?  So B refers to a column in a spreadsheet and 1 and 2 rows in the column?

To lookup values in a dataset you need to have a key variable for the dataset.  Is that what this means?  If so then why the heck would you want to divide the value in the second row by the value in the first row?  What meaning would that have?

[Tom · Accepted solution]

Sounds like you want replace the string A2 with the string B$1.

If the values are always one letter followed by one digit then TRANWRD() could do that.

length formula $30;
formula = 'A2 / A3';
formula = tranwrd(formula,'A2','B$1');
formula = tranwrd(formula,'A3','B$2');
...

If the values could be a letter followed by more than one digit.  Like A1, A2, ... A9, A10, A11 etc then you would need to do them in reverse order so that the A10 and A11 are replaced before the attempt to replace A1 happens.

formula = tranwrd(formula,'A11','B$10');
formula = tranwrd(formula,'A10','B$9');
...
formula = tranwrd(formula,'A1','B$0');

If it more complex then you will need to use regular expressions instead.

Once you work out what code will make the changes implied by HAVE1 then it just reduces to a code generation step of using the data in HAVE1 to generate the code needed to modify HAVE2.

proc sort data=have1 ;
  by descending num_a;
run;

filename code temp;
data _null_;
  file code;
  set have1;
  by descending NUM_A;
  put 'formula=tranwrd(formula,' num_a :$quote. ',' ser :$quote. ');' ;
run;

And then using the generated code:

data want;
  length num_b $2 formula $50 ;
  set have2;
%include code / source2;
run;

Result:

OBS    num_b     formula

 1      B2      B$1 / B$2
 2      B3      B$3 / B$4

Test:

Observation Summary                                                                                                                 
                                                                                                                                    
Observation      Base  Compare                                                                                                      
                                                                                                                                    
First Obs           1        1                                                                                                      
Last  Obs           2        2                                                                                                      
                                                                                                                                    
Number of Observations in Common: 2.                                                                                                
Total Number of Observations Read from WORK.EXPECT: 2.                                                                              
Total Number of Observations Read from WORK.WANT: 2.                                                                                
                                                                                                                                    
Number of Observations with Some Compared Variables Unequal: 0.                                                                     
Number of Observations with All Compared Variables Equal: 2.                                                                        
                                                                                                                                    
NOTE: No unequal values were found.                      

[J111]

Thanks for all the answers, I appreciate your time very much !