I have two datasets: A and B. I would like to merge them such that for each record in dataset A, every record in dataset B is matched and the values from A are retained until the next row of A is reached. A and B do not have an ID to match on.
See below for examples and desired results. I think the solution might require a set statement for dataset B inside a do loop with manual output control but I've been thwarted by the end of file processing.
Dataset A:
| ID | Time | X1 |
| 1 | 1 | 100 |
| 1 | 2 | 1000 |
| 2 | 1 | 200 |
| 2 | 2 | 2500 |
| 2 | 3 | 300 |
| 3 | 1 | 100 |
| 3 | 2 | 500 |
Dataset B:
| Horizon | Curve |
| 1 | 100 |
| 2 | 90 |
Desired Dataset:
| ID | Time | X1 | Horizon | Curve |
| 1 | 1 | 100 | 1 | 100 |
| 1 | 1 | 100 | 2 | 90 |
| 1 | 2 | 1000 | 1 | 100 |
| 1 | 2 | 1000 | 2 | 90 |
| 2 | 1 | 200 | 1 | 100 |
| 2 | 1 | 200 | 2 | 90 |
| 2 | 2 | 2500 | 1 | 100 |
| 2 | 2 | 2500 | 2 | 90 |
| 2 | 3 | 300 | 1 | 100 |
| 2 | 3 | 300 | 2 | 90 |
| 3 | 1 | 100 | 1 | 100 |
| 3 | 1 | 100 | 2 | 90 |
| 3 | 2 | 500 | 1 | 100 |
| 3 | 2 | 500 | 2 | 90 |
data A;
input ID Time X1;
datalines;
1 1 100
1 2 1000
2 1 200
2 2 2500
2 3 300
3 1 100
3 2 500
;
run;
data B;
input Horizon Curve;
datalines;
1 100
2 90
;
run;
data Want;
set A;
do row = 1 to obsnum;
set B point = row nobs = obsnum;
output;
end;
run;
It is just a CLASSIC Cartesian Product Problem.
data A;
input ID Time X1;
datalines;
1 1 100
1 2 1000
2 1 200
2 2 2500
2 3 300
3 1 100
3 2 500
;
run;
data B;
input Horizon Curve;
datalines;
1 100
2 90
;
run;
proc sql;
create table want as
select *
from A,B
order by ID,Time;
quit;
For the pure fun, the hash method:
data want;
set a;
if 0 then set b;
if _n_ = 1
then do;
declare hash b (dataset:"b");
b.definekey("horizon");
b.definedata("horizon","curve");
b.definedone();
declare hiter bi ("b");
end;
rc = bi.first();
do while (rc = 0);
output;
rc = bi.next();
end;
drop rc;
run;
Kurt,
Your code could be simpler as this:
rc = bi.first(); do while (rc = 0); output; rc = bi.next(); end; drop rc;
---------------> do while (bi.next() = 0); output; end;
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!
Still thinking about your presentation idea? The submission deadline has been extended to Friday, Nov. 14, at 11:59 p.m. ET.
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.
Ready to level-up your skills? Choose your own adventure.