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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.