merging problem

[Lida]

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!

[stat_sas]

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;

[Lida]

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.

[ballardw · Accepted solution]

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;

[Lida]

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