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