topic Re: Copy values across rows in SAS Programming

Copy values across rows

deleted_user — Wed, 10 Nov 2010 23:27:03 GMT

I have a data which looks like this and i want to copy the last obesrvation of height to all the previous rows of a given subject

Sub Date Age Rank
1 xxx 23
1 xxy 20
1 xyy 13
1 xyx 16 1
2 xyx 12
2 xyy 14
2 xzz 15 2

I would like to copy the rank value of a given subject to all the previous dates (rows)

Re: Copy values across rows

Doc_Duke — Thu, 11 Nov 2010 00:22:12 GMT

SAS doesn't really do a "read ahead", but it does readily do a "retain". If the data always have the rank at the end, you could sort by sub and descending rank and then use a data step and retain to propagate the values to the "new" forward.

Doc Muhlbaier
Duke

Re: Copy values across rows

Peter_C — Thu, 11 Nov 2010 09:16:22 GMT

although SAS does not "read ahead", this is not forecasting so just read it any way you want 😉

For a base SAS table, using the POINT= option of the SET statement, you can "read in reverse".[pre]data updated_data ;
do _n_ = top to 1 by -1 ;
set your.data point= _n_ nobs= top ;
if not missing(rank) then old_rank= rank ;
else rank = old_rank ;
output ;
end ;
stop ;
drop old_rank ;
run;[/pre]

peterC

Re: Copy values across rows

data_null__ — Thu, 11 Nov 2010 12:02:49 GMT

[pre]
data have;
infile cards missover;
input Sub Date$ Age Rank;
cards;
1 xxx 23
1 xxy 20
1 xyy 13
1 xyx 16 1
2 xyx 12
2 xyy 14
2 xzz 15 2
;;;;
run;
data need;
merge have(drop=rank) have(keep=sub rank where=(not missing(rank)));
by sub;
run;
proc print;
run;
[/pre]

Re: Copy values across rows

Peter_C — Thu, 11 Nov 2010 12:56:14 GMT

good demo _null_
my backward solution pays no respect to subject

peterC