copying value from the next observation

Occasional Contributor
Posts: 16

copying value from the next observation

Hi,

I believe there must be a simple way to copy value in the next row(observation) within the same column.

Data set can be simplifies as below:

[original data set]

V1  V2  V3  V4  V5

aa   bb  cc  dd   ee

mk  jk   as   rd   as

aa   bb  cc  dd   ee

re    te   sf   er   qw

[goal data set]

V1  V2  V3  V4   V5

mk   bb  as   dd  ee

mk   jk   as   rd   as

re    bb   sf   dd   ee

re    te    sf   er   qw

I would like to change the value 'aa' into the value in the next row

and change the value 'cc' into the value in the next row.

Any comment or suggestion will be greatly appreciated.

Thanks,

Minsoo

Super User
Posts: 8,115

copying value from the next observation

This is a common problem.  For example look at this thread.

For your problem you could try:

data want ;

set have nobs=nobs ;

next1 = _n_ + 1;

if V1 in ('aa','cc') and _n_ < nobs then set have(keep=V1) point=next1 ;

run;

You will need to expand this to cover all the variables that you want to "move up".

PROC Star
Posts: 8,164

copying value from the next observation

A slight variation on Tom's suggested solution:

data want;

set have;

next1 = _n_ + 1;

if mod(_n_,2) eq 1 then set have(keep=V1) point=next1 ;

run;

Super User
Posts: 10,784

copying value from the next observation

Or use merge skill

```data temp;
input (V1-V5 ) (\$) ;
cards;
aa   bb  cc  dd   ee
mk  jk   as   rd   as
aa   bb  cc  dd   ee
re    te   sf   er   qw
;run;
data want;
merge temp temp(firstobs=2 keep=v1 v3 rename=(v1=_v1 v3=_v3));
if v1 eq 'aa' then v1=_v1;
if v3 eq 'cc' then v3=_v3;
drop _:;
run;

```

Ksharp

Frequent Contributor
Posts: 139

copying value from the next observation

One other method:

data temp;
input (V1-V5 ) (\$) ;
cards;
aa   bb  cc  dd   ee
mk  jk   as   rd   as
aa   bb  cc  dd   ee
re    te   sf   er   qw
;run;

data temp1;
set temp;
N=_n_;
run;
proc sort data=temp1 ;
by descending n;
run;

data temp1;
set temp1; lagv1=lag(v1);lagv3=lag(v3);
if v1='aa' then v1=lagv1;if v3='cc' then v3=lagv3;
run;

proc sort data =temp1 out=temp(drop=n lagv1 lagv3);
by n;
run;

Discussion stats
• 4 replies
• 5036 views
• 0 likes
• 5 in conversation