topic Re: Many to many merge without ID in SAS Programming

Many to many merge without ID

sjb1 — Mon, 25 Aug 2025 23:19:45 GMT

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

Re: Many to many merge without ID

SASKiwi — Tue, 26 Aug 2025 00:07:03 GMT

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;

Re: Many to many merge without ID

Ksharp — Tue, 26 Aug 2025 03:05:03 GMT

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;

Re: Many to many merge without ID

Kurt_Bremser — Tue, 26 Aug 2025 07:16:22 GMT

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;

Re: Many to many merge without ID

Ksharp — Tue, 26 Aug 2025 07:44:53 GMT

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;