Combine data from 2 files which has duplicate keys

[gandikk]

Hi,

I have 2 files on mainframe and both files have duplicate keys. 

first file:

ID	KEY
1234	5678
3456	5678

second file:

KEY	DATA
5678	abc.com
5678	ghi.com
5678	xyz.com

first and second files have duplicate keys. I need to repeat 3 records from 2nd file for each record in first file. My final output should be like this:

KEY	DATA	ID
5678	abc.com	1234
5678	ghi.com	1234
5678	xyz.com	1234
5678	abc.com	3456
5678	ghi.com	3456
5678	xyz.com	3456

Can I achieve this in SAS mainframe. 

Thanks for your help in advance!!

[SASJedi · Accepted solution]

You could use a DATA step merge or an SQL join. The join is simpler to code. 

First, crate the input data:

data h1;
	infile datalines dsd dlm=' ';
	input id  key;
datalines;
1234 5678
3456 5678
;

data h2;
	infile datalines dsd dlm=' ';
	input key data:$10.;
datalines;
5678 abc.com
5678 ghi.com
5678 xyz.com
;

Then do the join:

proc sql number;
select h2.*, Id
	from h1
	  inner join
         h2
	on h1.key=h2.key
	order by id, data
;
quit;