topic Re: Fill in the blank in SAS Programming

Fill in the blank

ursula — Thu, 15 Nov 2018 00:10:28 GMT

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.

Re: Fill in the blank

Reeza — Thu, 15 Nov 2018 00:39:08 GMT

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.

Re: Fill in the blank

ursula — Thu, 15 Nov 2018 00:46:04 GMT

Thank you very much Reeza.

it works!

Re: Fill in the blank

novinosrin — Thu, 15 Nov 2018 01:58:49 GMT

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;

Re: Fill in the blank

ursula — Fri, 16 Nov 2018 22:19:39 GMT

Thank you, Novi.

it looks very simple and it works!

but don't understand how it works.