Hello,
my data looks like that:
year ID INDICAOTR
1990 Y 1
1991 x 0
1993 x 1
1994 x 0
1995 x 0
1996 x 2
I look for a code that will change the rows ABOVE same ID to have the last indicator. what I want my data to look like is:
year ID INDICAOTR
1990 y 1
1991 x 1
1993 x 1
1994 x 2
1995 x 2
1996 x 2
Assuming your actual data is more like this where YEAR is nested within ID
data have;
input id $ year indicator;
cards;
x 1991 0
x 1993 1
x 1994 0
x 1995 0
x 1996 2
y 1990 1
;
Then you could use a double DOW loop. The first loop to find the next non zero indicator. And the second the read back the same observations and write them out with the new calculated variable.
data want;
do _n_=1 by 1 until (last.id or indicator);
set have;
by id year;
end;
new_indicator=indicator;
do _n_=1 to _n_;
set have;
output;
end;
drop indicator;
rename new_indicator=indicator;
run;
Results:
Obs id year indicator 1 x 1991 1 2 x 1993 1 3 x 1994 2 4 x 1995 2 5 x 1996 2 6 y 1990 1
Here's a program that produces the results in your post.
data want;
do until (last.id or indicaotr ne 0);
set have;
by id notsorted;
end;
final_indicaotr = indicaotr;
do until (last.id or indicaotr ne 0);
set have;
by id notsorted;
output;
end;
run;
I hope I picked out the right result from your example. Try it and see.
Assuming your actual data is more like this where YEAR is nested within ID
data have;
input id $ year indicator;
cards;
x 1991 0
x 1993 1
x 1994 0
x 1995 0
x 1996 2
y 1990 1
;
Then you could use a double DOW loop. The first loop to find the next non zero indicator. And the second the read back the same observations and write them out with the new calculated variable.
data want;
do _n_=1 by 1 until (last.id or indicator);
set have;
by id year;
end;
new_indicator=indicator;
do _n_=1 to _n_;
set have;
output;
end;
drop indicator;
rename new_indicator=indicator;
run;
Results:
Obs id year indicator 1 x 1991 1 2 x 1993 1 3 x 1994 2 4 x 1995 2 5 x 1996 2 6 y 1990 1
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: