Hi there,
I wonder if some one can help me for the codes as the following data of what I want.
basically, I want to fill the blank values with the closest previous values by ID.
ID | VALUE | WANT |
A | 10 | 10 |
A | 10 | |
A | 20 | 20 |
A | 20 | 20 |
A | 20 | |
A | 20 | |
A | 5 | 5 |
A | 5 | |
A | 25 | 25 |
A | 25 | |
B | 100 | 100 |
B | 200 | 200 |
B | 200 | |
B | 250 | 250 |
B | 250 | |
C | 300 | 300 |
C | 300 | |
C | 300 | |
C | 300 | |
C | 400 | 400 |
data test;
input ID $ value;
datalines;
A 10
A .
A 20
A 20
A .
A .
A 5
A .
A 25
A .
B 100
B 200
B .
B 250
B .
C 300
C .
C .
C .
C 400
;
RUN;
thank you in advance.
data want;
set have;
by id;
retain new_value;
if first.id then new_value=value;
if not missing(value) then new_value=value;
*drop value;
*rename new_value = value;
run;
@ursula wrote:
Hi there,
I wonder if some one can help me for the codes as the following data of what I want.
basically, I want to fill the blank values with the closest previous values by ID.
ID VALUE WANT A 10 10 A 10 A 20 20 A 20 20 A 20 A 20 A 5 5 A 5 A 25 25 A 25 B 100 100 B 200 200 B 200 B 250 250 B 250 C 300 300 C 300 C 300 C 300 C 400 400
data test;
input ID $ value;
datalines;
A 10
A .
A 20
A 20
A .
A .
A 5
A .
A 25
A .
B 100
B 200
B .
B 250
B .
C 300
C .
C .
C .
C 400
;
RUN;
thank you in advance.
data want;
set have;
by id;
retain new_value;
if first.id then new_value=value;
if not missing(value) then new_value=value;
*drop value;
*rename new_value = value;
run;
@ursula wrote:
Hi there,
I wonder if some one can help me for the codes as the following data of what I want.
basically, I want to fill the blank values with the closest previous values by ID.
ID VALUE WANT A 10 10 A 10 A 20 20 A 20 20 A 20 A 20 A 5 5 A 5 A 25 25 A 25 B 100 100 B 200 200 B 200 B 250 250 B 250 C 300 300 C 300 C 300 C 300 C 400 400
data test;
input ID $ value;
datalines;
A 10
A .
A 20
A 20
A .
A .
A 5
A .
A 25
A .
B 100
B 200
B .
B 250
B .
C 300
C .
C .
C .
C 400
;
RUN;
thank you in advance.
Thank you very much Reeza.
it works!
data test;
input ID $ value;
datalines;
A 10
A .
A 20
A 20
A .
A .
A 5
A .
A 25
A .
B 100
B 200
B .
B 250
B .
C 300
C .
C .
C .
C 400
;
RUN;
data want;
update test(obs=0) test;
by id;
output;
run;
Thank you, Novi.
it looks very simple and it works!
but don't understand how it works.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.