topic Re: How to replace updated values with the original ones? in SAS Programming

How to replace updated values with the original ones?

Financier — Thu, 19 Jan 2017 02:50:04 GMT

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!

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

PGStats — Thu, 19 Jan 2017 03:46:48 GMT

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;

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

Financier — Thu, 19 Jan 2017 20:05:03 GMT

Thank you! It works.