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!
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;
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;
Thank you! It works.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.