Solved
Contributor
Posts: 42

# merging problem

Hello there, how I can merge two files like this

File1

ID

1055

1058

1059

With file2

 group 1 2 3 4 5 6

To get a file that looks like this:

ID            group

1055       1

1055       2

1055       3

1055       4

1055       5

1055       6

1058       1

1058       2

1058       3

1058       4

1058       5

1058       6

1059       1

1059       2

1059       3

1059       4

1059       5

1059       6

I tried to create one common variable (common=1) in each file and set it to 1 and than merge the two files by that var but it did not get me what I want.

data test;

merge file1 file2;

by common;

run;

What do I do wrong?

Thank you!

Accepted Solutions
Solution
‎08-09-2016 04:08 PM
Super User
Posts: 13,583

## Re: merging problem

Assuming that you have an issue where the second set is actually somewhat more complex and can't be duplicated with a do loop this would be referred to as a cartesian join where every element of one set is matched with every element of another.

Proc Sql;

create table want as

select File1.*, File2.*

from File1, File2;

quit;

All Replies
Posts: 1,270

## Re: merging problem

Hi,

Why you need to merge. This can be done in this way:

data File1;
input ID;
datalines;
1055
1058
1059
;

data want;
set File1;
do group=1 to 6;
output;
end;
run;

Contributor
Posts: 42

## Re: merging problem

That was just an example. I have many records in both files, that looks more complicated than 1,2,..6. So I would prefer to merge those two file in a way that each record from the file 1 picked up all records from the file2.

Solution
‎08-09-2016 04:08 PM
Super User
Posts: 13,583

## Re: merging problem

Assuming that you have an issue where the second set is actually somewhat more complex and can't be duplicated with a do loop this would be referred to as a cartesian join where every element of one set is matched with every element of another.

Proc Sql;

create table want as

select File1.*, File2.*

from File1, File2;

quit;

Contributor
Posts: 42

## Re: merging problem

Oh, great! It worked!!! Thank you very much! Your help is very much appreciated!

☑ This topic is solved.