BookmarkSubscribeRSS Feed
Maria_Frankfurt
Calcite | Level 5

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.

10 REPLIES 10
Reeza
Super User

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

Maria_Frankfurt
Calcite | Level 5

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

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

Maria_Frankfurt
Calcite | Level 5

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

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
Calcite | Level 5

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

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
Calcite | Level 5

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

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.

 

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
  • 10 replies
  • 5990 views
  • 0 likes
  • 3 in conversation