topic copying value from the next observation in SAS Programming

copying value from the next observation

ibsulkim — Fri, 15 Jul 2011 22:07:57 GMT

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

copying value from the next observation

Tom — Fri, 15 Jul 2011 22:25:45 GMT

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

http://listserv.uga.edu/cgi-bin/wa?A2=ind1012B&L=sas-l&P=R14426&D=0

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".

copying value from the next observation

art297 — Sat, 16 Jul 2011 04:17:13 GMT

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;

copying value from the next observation

Ksharp — Mon, 18 Jul 2011 07:04:04 GMT

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

copying value from the next observation

manojinpec — Mon, 18 Jul 2011 10:20:48 GMT

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;