Merge two tables with different number of columns

Reply
Occasional Contributor
Posts: 5

Merge two tables with different number of columns

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.

Super User
Posts: 21,546

Re: Merge two tables with different number of columns

Posted in reply to Maria_Frankfurt

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

Occasional Contributor
Posts: 5

Re: Merge two tables with different number of columns

Posted in reply to 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.

 

Super User
Posts: 21,546

Re: Merge two tables with different number of columns

Posted in reply to Maria_Frankfurt

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

Occasional Contributor
Posts: 5

Re: Merge two tables with different number of columns

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.

 

 

 

Super User
Posts: 21,546

Re: Merge two tables with different number of columns

Posted in reply to Maria_Frankfurt
Unfortunately this is more than I have time for tonight, if someone else doesn't answer this I'll take another look tomorrow.
Super User
Posts: 8,590

Re: Merge two tables with different number of columns

Posted in reply to Maria_Frankfurt

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Merge two tables with different number of columns

Posted in reply to KurtBremser

@KurtBremser: 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.

 

Super User
Posts: 8,590

Re: Merge two tables with different number of columns

Posted in reply to Maria_Frankfurt

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Merge two tables with different number of columns

Posted in reply to KurtBremser

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.

 

Super User
Posts: 8,590

Re: Merge two tables with different number of columns

Posted in reply to Maria_Frankfurt

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 10 replies
  • 171 views
  • 0 likes
  • 3 in conversation