Merge two tables with different number of columns

[Maria_Frankfurt]

Hello,

I want to merge two tables with different number of lines in a horizontal layout. Table 1 has two rows, table 2 has six rows. The goal (table 3) is a table with five rows. The two rows are like a header, table 2 includes the data that belongs to those sentences.

I have tried different ways to merge the tables. I have either two many sentences, or some data is missing.

Please check the attachments, for the data. I think hat can explain better what I want.

I work in SAS Enterprise Guide with a sas programm on Version 9.2.

Thank you.

[Reeza]

Please post your data directly into the forum, many users will not download attachments. 

[Maria_Frankfurt]

Table 1

Key1	Tab1_Field1	Tab1_Field2	Tab1_Field3	Tab1_Field4	Tab1_Field5	Tab1_Field6
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	000000000200000	00
00RGT16GNT187GWA	280688009000	USD	EKALL	00	00	00

Table 2

Key2	Tab2_Field1	Tab2_Field2	Tab2_Field3	Tab2_Field4	Tab2_Field5	Tab2_Field6	Tab2_Field7	Tab2_Field8
B2NG03W443Y97IW0	000000000000000	1	W	01	KRW	4,00000000	280688009000	EKALL
B2NG03W443Y97IW0	000000000000000	1	W	01	ISK	0,50000000	280688009000	EKALL
B2NG03W443Y97IW0	000000000000000	1	W	01	ILS	0,05700000	280688009000	EKALL
B2NG03W443Y97IW0	000000000000000	1	W	01	RON	0,02500000	280688009000	EKALL
B2NG03W443Y97IW0	000000000200000	2	W	02	KRW	4,00000000	280688009000	EKALL
B2NG03W443Y97IW0	000000000200000	2	W	02	ILS	0,05700000	280688009000	EKALL

Table3

Key1	Tab1_Field1	Tab1_Field2	Tab1_Field3	Tab1_Field4	Tab2_Field2	Tab2_Field3	Tab2_Field4	Tab2_Field5	Tab2_Field6	Tab2_Field7	Tab2_Field8	Tab1_Field5	Tab2_Field2	Tab2_Field3	Tab2_Field4	Tab2_Field5	Tab2_Field6	Tab2_Field7	Tab2_Field8	Tab1_Field6	Tab2_Field2	Tab2_Field3	Tab2_Field4	Tab2_Field5	Tab2_Field6	Tab2_Field7	Tab2_Field8
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	KRW	4,00000000	280688009000	EKALL	000000000200000	2	W	02	KRW	4,00000000	280688009000	EKALL	00
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	ISK	0,50000000	280688009000	EKALL	000000000200000	2	W	02	ILS	0,05700000	280688009000	EKALL	00
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	ILS	0,05700000	280688009000	EKALL									00
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	RON	0,02500000	280688009000	EKALL									00
00RGT16GNT187GWA	280688009000	USD	EKALL	00																00

In the Excelfiles are some columns coloured, I hope that will explain the problem better.

[Reeza]

Ok...what are the rules here for creating Table 3?

[Maria_Frankfurt]

Hi Reeza,

Merge the tables with key1, it's unique.

Merge the tables with key1, it's unique.

Tab1_Field1, Tab1_Field2, Tab1_Field3 are header fields.

match Tab1_Field4 with Tab2_Field1 as a group 1 with Tab2_Field2 Tab2_Field3 Tab2_Field4 Tab2_Field5 Tab2_Field6 Tab2_Field7 Tab2_Field8
match Tab1_Field5 with Tab2_Field1 as a group 2 with Tab2_Field2 Tab2_Field3 Tab2_Field4 Tab2_Field5 Tab2_Field6 Tab2_Field7 Tab2_Field8
match Tab1_Field6 with Tab2_Field1 as a group 3 with Tab2_Field2 Tab2_Field3 Tab2_Field4 Tab2_Field5 Tab2_Field6 Tab2_Field7 Tab2_Field8

The goal is to match the three groups on Key1 and on the Tab1_Field4, Tab1_Field5, Tab1_Field6.

Result is in table 3.

I hope this helps to understand.

[Reeza]

Unfortunately this is more than I have time for tonight, if someone else doesn't answer this I'll take another look tomorrow.

[Kurt_Bremser]

The easiest way I see is to transpose table_1 so that fields 4, 5 and 6 are stacked vertically, then do the merge, and then re-transpose to the wide format.

If the wide format is needed at all, as it's usually easier in SAS to stay in a long format.

[Maria_Frankfurt]

@Kurt_Bremser: Thank you for your advice. I tried transpose and I got a little further. But it is still not working how I want it.

First: I have to update my table3, my desired result.

My Goal looks like this:

Key1	Tab1_Field1	Tab1_Field2	Tab1_Field3	Tab1_Field4	Tab2_Field2	Tab2_Field3	Tab2_Field4	Tab2_Field5	Tab2_Field6	Tab2_Field7	Tab2_Field8	Tab1_Field5	Tab2_Field2	Tab2_Field3	Tab2_Field4	Tab2_Field5	Tab2_Field6	Tab2_Field7	Tab2_Field8	Tab1_Field6	Tab2_Field2	Tab2_Field3	Tab2_Field4	Tab2_Field5	Tab2_Field6	Tab2_Field7	Tab2_Field8
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	KRW	4,00000000	280688009000	EKALL	000000000200000	2	W	02	KRW	4,00000000	280688009000	EKALL	00
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	ISK	0,50000000	280688009000	EKALL	000000000200000	2	W	02	ILS	0,05700000	280688009000	EKALL	00
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	ILS	0,05700000	280688009000	EKALL									00
B2NG03W443Y97IW0	280688009000	EUR	EKALL	000000000000000	1	W	01	RON	0,02500000	280688009000	EKALL									00
00RGT16GNT187GWA	280688009000	USD	EKALL	00								00								00

To make it easer to understand my problem, I created table01 and table02 in sas. I was able to transpose table01 and then merge table01 and table02.

But after that I am stuck, I don't know how to transpose my result back to horizontal layout to match my table3.

Here ist my code:

data table01;

format key1 $16. Tab1_Field1 $13. Tab1_Field2 $3. Tab1_Field3 $5. Tab1_Field4_1 $15. Tab1_Field4_2 $15. Tab1_Field4_3 $15. ;

input id key1 Tab1_Field1 Tab1_Field2 Tab1_Field3 Tab1_Field4_1 Tab1_Field4_2 Tab1_Field4_3 ;

cards;

1 00RGT16GNT187GWA 280688009000 USD EKALL 00 00 00

2 B2NG03W443Y97IW0 280688009000 EUR EKALL 000000000000000 000000000200000 00

;

run;

data table02;

format key2 $16. Tab2_Field1 $15. Tab2_Field2 $1. Tab2_Field3 $1. Tab2_Field4 $2.

Tab2_Field5 $3. Tab2_Field6 10.8 Tab2_Field7 $13. Tab2_Field8 $5. ;

input id key2 Tab2_Field1 Tab2_Field2 Tab2_Field3 Tab2_Field4 Tab2_Field5 Tab2_Field6 Tab2_Field7 Tab2_Field8 ;

cards;

1 B2NG03W443Y97IW0 000000000000000 1 W 01 KRW 4. 280688009000 EKALL

2 B2NG03W443Y97IW0 000000000000000 1 W 01 ISK 0.5 280688009000 EKALL

3 B2NG03W443Y97IW0 000000000000000 1 W 01 ILS 0.57 280688009000 EKALL

4 B2NG03W443Y97IW0 000000000000000 1 W 01 RON 0.025 280688009000 EKALL

5 B2NG03W443Y97IW0 000000000200000 2 W 02 KRW 4. 280688009000 EKALL

6 B2NG03W443Y97IW0 000000000200000 2 W 02 ILS 0.057 280688009000 EKALL

;

run;

PROC TRANSPOSE DATA=table01 OUT=table01_v NAME=Tab1_Field4 PREFIX=Value;

VAR Tab1_Field4_1 Tab1_Field4_2 Tab1_Field4_3;

BY key1 Tab1_Field1 Tab1_Field2 Tab1_Field3 ;

RUN;

　

proc sql;

create table merge as

select

t1.key1

, t1.Tab1_Field1

, t1.Tab1_Field2

, t1.Tab1_Field3

, t1.value1

, t2.Tab2_Field2

, t2.Tab2_Field3

, t2.Tab2_Field4

, t2.Tab2_Field5

, t2.Tab2_Field6

, t2.Tab2_Field7

, t2.Tab2_Field8

from table01_v as t1 left outer join

table02 as t2

on t1.key1 = t2.key2

and t1.Value1 = t2.Tab2_Field1

order by t1.key1

;

quit;

I appreciate your help very much and hope for another hint. Thank you in advance.

[Kurt_Bremser]

Please use the "little running man" icon to post code, as this happens when I copy/paste your code to Enterprise Guide:

data table01;

format key1 $16. Tab1_Field1 $13. Tab1_Field2 $3. Tab1_Field3 $5. Tab1_Field4_1 $15. Tab1_Field4_2 $15. Tab1_Field4_3 $15. ;

input id key1 Tab1_Field1 Tab1_Field2 Tab1_Field3 Tab1_Field4_1 Tab1_Field4_2 Tab1_Field4_3 ;

cards;

1 00RGT16GNT187GWA 280688009000 USD EKALL 00 00 00

2 B2NG03W443Y97IW0 280688009000 EUR EKALL 000000000000000 000000000200000 00

;

run;

data table02;

format key2 $16. Tab2_Field1 $15. Tab2_Field2 $1. Tab2_Field3 $1. Tab2_Field4 $2.

Tab2_Field5 $3. Tab2_Field6 10.8 Tab2_Field7 $13. Tab2_Field8 $5. ;

input id key2 Tab2_Field1 Tab2_Field2 Tab2_Field3 Tab2_Field4 Tab2_Field5 Tab2_Field6 Tab2_Field7 Tab2_Field8 ;

cards;

1 B2NG03W443Y97IW0 000000000000000 1 W 01 KRW 4. 280688009000 EKALL

2 B2NG03W443Y97IW0 000000000000000 1 W 01 ISK 0.5 280688009000 EKALL

3 B2NG03W443Y97IW0 000000000000000 1 W 01 ILS 0.57 280688009000 EKALL

4 B2NG03W443Y97IW0 000000000000000 1 W 01 RON 0.025 280688009000 EKALL

5 B2NG03W443Y97IW0 000000000200000 2 W 02 KRW 4. 280688009000 EKALL

6 B2NG03W443Y97IW0 000000000200000 2 W 02 ILS 0.057 280688009000 EKALL

;

run;

PROC TRANSPOSE DATA=table01 OUT=table01_v NAME=Tab1_Field4 PREFIX=Value;

VAR Tab1_Field4_1 Tab1_Field4_2 Tab1_Field4_3;

BY key1 Tab1_Field1 Tab1_Field2 Tab1_Field3 ;

RUN;

　

proc sql;

create table merge as

select

t1.key1

, t1.Tab1_Field1

, t1.Tab1_Field2

, t1.Tab1_Field3

, t1.value1

, t2.Tab2_Field2

, t2.Tab2_Field3

, t2.Tab2_Field4

, t2.Tab2_Field5

, t2.Tab2_Field6

, t2.Tab2_Field7

, t2.Tab2_Field8

from table01_v as t1 left outer join

table02 as t2

on t1.key1 = t2.key2

and t1.Value1 = t2.Tab2_Field1

order by t1.key1

;

quit;

[Maria_Frankfurt]

Hi Kurt Bremser,

I can not change the layout. I have to deliever an excelfile with an overview over priceconditions with many varieties. The layout was designed by the recipient.

data table01;
format key1 $16. Tab1_Field1 $13. Tab1_Field2 $3. Tab1_Field3 $5. Tab1_Field4_1 $15. Tab1_Field4_2 $15. Tab1_Field4_3 $15. ;
input id key1  Tab1_Field1  Tab1_Field2  Tab1_Field3  Tab1_Field4_1  Tab1_Field4_2  Tab1_Field4_3 ;
cards;
1 00RGT16GNT187GWA 280688009000 USD EKALL 00              00              00 
2 B2NG03W443Y97IW0 280688009000 EUR EKALL 000000000000000 000000000200000 00 
;
run;

data table02;
format key2 $16. Tab2_Field1 $15. Tab2_Field2 $1. Tab2_Field3 $1. Tab2_Field4 $2. 
                 Tab2_Field5 $3. Tab2_Field6 10.8 Tab2_Field7 $13. Tab2_Field8 $5. ;
input id key2  Tab2_Field1  Tab2_Field2  Tab2_Field3  Tab2_Field4  Tab2_Field5  Tab2_Field6  Tab2_Field7  Tab2_Field8 ;
cards;
1 B2NG03W443Y97IW0 000000000000000 1 W 01 KRW 4. 280688009000 EKALL
2 B2NG03W443Y97IW0 000000000000000 1 W 01 ISK 0.5 280688009000 EKALL
3 B2NG03W443Y97IW0 000000000000000 1 W 01 ILS 0.57 280688009000 EKALL
4 B2NG03W443Y97IW0 000000000000000 1 W 01 RON 0.025 280688009000 EKALL
5 B2NG03W443Y97IW0 000000000200000 2 W 02 KRW 4. 280688009000 EKALL
6 B2NG03W443Y97IW0 000000000200000 2 W 02 ILS 0.057 280688009000 EKALL
;
run;

  PROC TRANSPOSE DATA=table01 OUT=table01_v NAME=Tab1_Field4 PREFIX=Value;
   VAR Tab1_Field4_1  Tab1_Field4_2  Tab1_Field4_3;
   BY  key1  Tab1_Field1  Tab1_Field2  Tab1_Field3 ;
   RUN;


proc sql;
create table merge as 
      select
  t1.key1
, t1.Tab1_Field1
, t1.Tab1_Field2
, t1.Tab1_Field3
, t1.value1
, t2.Tab2_Field2
, t2.Tab2_Field3
, t2.Tab2_Field4
, t2.Tab2_Field5
, t2.Tab2_Field6
, t2.Tab2_Field7
, t2.Tab2_Field8
 from table01_v as t1 left outer join
	  table02   as t2
   on t1.key1  = t2.key2
  and t1.Value1 = t2.Tab2_Field1
order by t1.key1
       ;
quit;

Here is the code again.

[Kurt_Bremser]

I still question any reason for such an unwieldy and complicated dataset structure, that will only make working with the data harder and solves (IMHO) no purpose.