DATA Step, Macro, Functions and more

How to replace updated values with the original ones?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to replace updated values with the original ones?

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!


Accepted Solutions
Solution
‎01-19-2017 03:04 PM
Respected Advisor
Posts: 4,920

Re: How to replace updated values with the original ones?

Posted in reply to Financier

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;
PG

View solution in original post

Attachment

All Replies
Solution
‎01-19-2017 03:04 PM
Respected Advisor
Posts: 4,920

Re: How to replace updated values with the original ones?

Posted in reply to Financier

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;
PG
Attachment
Occasional Contributor
Posts: 7

Re: How to replace updated values with the original ones?

Thank you! It works.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 159 views
  • 2 likes
  • 2 in conversation