How to replace updated values with the original ones?

[Financier]

Hi all,

I have a dataset with multiple updates of the initial value. Data is generated in such a way that new value becomes a reference number in case of subsequent update. There are multiple update sequences without any particular order and of different length. My goal is to add a column with the original old value for all rows belonging to the same sequence of updates.

Here is how it looks like

old new

1 2

2 5

3 4

7 8

4 6

5 9

Here is how I want it to look like:

old new original

1 2 1

2 5 1

3 4 3

7 8 7

4 6 3

5 9 1

Thank you!

[PGStats · Accepted solution]

Assuming that new is greater than old, you can use the subgraphs macro (attached):

data have;
input old new;
datalines;
1 2
2 5
3 4
7 8
4 6
5 9
;

%include "&sasforum.\subgraphsmacro.sas" / source2;

%subgraphs(have,from=old,to=new);

proc sql;

create table origin as
select node, min(node) as original
from clusters
group by clust;

create table want as
select have.*, original
from have, origin
where old=node;

quit;

[Financier]

Thank you! It works.