Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- How to create a table compsed out of 2 translating tables

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-29-2022 06:31 AM
(659 views)

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 ;

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hello,

"Long" meaning hundreds and not only 2 obs.

A code would be wonderful. Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 !

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 ;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

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.