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!!
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.