BookmarkSubscribeRSS Feed
sjb1
Fluorite | Level 6

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:

IDTimeX1
11100
121000
21200
222500
23300
31100
32500

 

Dataset B: 

HorizonCurve
1100
290

 

Desired Dataset:

IDTimeX1HorizonCurve
111001100
11100290
1210001100
121000290
212001100
21200290
2225001100
222500290
233001100
23300290
311001100
31100290
325001100
32500290
4 REPLIES 4
SASKiwi
PROC Star
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;
Ksharp
Super User

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;

 

 

Kurt_Bremser
Super User

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;
Ksharp
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 299 views
  • 3 likes
  • 4 in conversation