HI @Sarah2913 You can replicate if you have rownum for each by group akin to DB sql like oracle or teradata i.e partition by
data a;
input a;
cards;
1
1
2
2
2
2
2
3
3
;
/*Partition by rownum for each by group*/
data a;
set a;
by a;
if first.a then n=1;
else n+1;
run;
data b;
input a;
cards;
1
1
1
2
3
3
3
;
/*Partition by rownum for each by group*/
data b;
set b;
by a;
if first.a then n1=1;
else n1+1;
run;
data want_merge(drop=n:);
merge a(in=in1) b(in=in2);
by a;
if in1 and in2;
run;
/*replicate merge using many to many to make it one to many*/
proc sql;
create table want_sql(drop=n2) as
select distinct a.a,b.a as b,(max(n,n1)) as n2
from a a inner join b b
on a.a=b.a;
quit;
Of course the above does involve some logic , nevertheless that's a fun experiment
... View more
