DATA Step, Macro, Functions and more

merging problem

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

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: 11,343

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;

 

 

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

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: 36

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: 11,343

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: 36

Re: merging problem

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 292 views
  • 0 likes
  • 3 in conversation