🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS Employee

## Need Help solving an analytical problem

Hello!
I have a table with data. One of the columns is filled with zeros, ones, and missings.
I need to fill in missings with four different options (and create four new columns).

Option 1:
empty values are filled as follows:

 1 1 0 0 1 1 0 0 … 1 … 0 … 1 … 1 … 1 … 0 … 1 … 1 … 1 … 0 … 1 … 1 … 1 … 0 … 1 … 1 1 1 0 0 0 0 1 1

Option 2:
empty values are filled as follows:

 1 1 0 0 1 1 0 0 … 1 … 0 … 0 … 0 … 1 … 0 … 0 … 0 … 1 … 0 … 0 … 0 … 1 … 0 … 0 … 0 1 1 0 0 0 0 1 1

Option 3:
empty values are filled as follows:

 1 1 0 0 1 1 0 0 … 1 … 0 … 1 … 0 … 1 … 0 … 1 … 0 … 1 … 0 … 1 … 0 … 1 … 0 … 1 … 0 1 1 0 0 0 0 1 1

Option 4:
empty values are filled as follows:

 1 1 0 0 1 1 0 0 … 1 … 0 … 0 … 1 … 1 … 0 … 0 … 1 … 1 … 0 … 0 … 1 … 1 … 0 … 0 … 1 1 1 0 0 0 0 1 1

This is probably not the most difficult task, but I'm a beginner, and I will be really grateful for the help!
(I suspect that the problem can be solved through the retain operator, but I would like to see the specific syntax from beginning to end)

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: Need Help solving an analytical problem

RETAIN is indeed useful here. And some other tricks, because the filling value depends both on the previous and next non missing values:

``````data have;
input x;
datalines;
.
1
.
.
1
.
.
.
0
0
.
0
.
.
.
1
1
;

data want;
retain fx;
do i = 1 by 1 until(not missing(x));
set have;
end;
lx = x;
do j = 1 to i;
set have;
if missing(x) then do;
if missing(fx) then call missing(x1, x2, x3, x4);
else if fx = lx then do;
x1 = fx; x2 = fx; x3 = fx; x4 = fx;
end;
else if fx = 1 then do;
x1 = 1; x2 = 0; x3 = 1; x4 = 0;
end;
else do;
x1 = 1; x2 = 0; x3 = 0; x4 = 1;
end;
end;
else do;
x1 = x; x2 = x; x3 = x; x4 = x;
end;
output;
end;
fx = lx;
drop fx lx i j;
run;

proc print data=want noobs; run;``````
```x 	x1 	x2 	x3 	x4
. 	. 	. 	. 	.
1 	1 	1 	1 	1
. 	1 	1 	1 	1
. 	1 	1 	1 	1
1 	1 	1 	1 	1
. 	1 	0 	1 	0
. 	1 	0 	1 	0
. 	1 	0 	1 	0
0 	0 	0 	0 	0
0 	0 	0 	0 	0
. 	0 	0 	0 	0
0 	0 	0 	0 	0
. 	1 	0 	0 	1
. 	1 	0 	0 	1
. 	1 	0 	0 	1
1 	1 	1 	1 	1
1 	1 	1 	1 	1```
PG
2 REPLIES 2
Opal | Level 21

## Re: Need Help solving an analytical problem

RETAIN is indeed useful here. And some other tricks, because the filling value depends both on the previous and next non missing values:

``````data have;
input x;
datalines;
.
1
.
.
1
.
.
.
0
0
.
0
.
.
.
1
1
;

data want;
retain fx;
do i = 1 by 1 until(not missing(x));
set have;
end;
lx = x;
do j = 1 to i;
set have;
if missing(x) then do;
if missing(fx) then call missing(x1, x2, x3, x4);
else if fx = lx then do;
x1 = fx; x2 = fx; x3 = fx; x4 = fx;
end;
else if fx = 1 then do;
x1 = 1; x2 = 0; x3 = 1; x4 = 0;
end;
else do;
x1 = 1; x2 = 0; x3 = 0; x4 = 1;
end;
end;
else do;
x1 = x; x2 = x; x3 = x; x4 = x;
end;
output;
end;
fx = lx;
drop fx lx i j;
run;

proc print data=want noobs; run;``````
```x 	x1 	x2 	x3 	x4
. 	. 	. 	. 	.
1 	1 	1 	1 	1
. 	1 	1 	1 	1
. 	1 	1 	1 	1
1 	1 	1 	1 	1
. 	1 	0 	1 	0
. 	1 	0 	1 	0
. 	1 	0 	1 	0
0 	0 	0 	0 	0
0 	0 	0 	0 	0
. 	0 	0 	0 	0
0 	0 	0 	0 	0
. 	1 	0 	0 	1
. 	1 	0 	0 	1
. 	1 	0 	0 	1
1 	1 	1 	1 	1
1 	1 	1 	1 	1```
PG
SAS Employee

## Re: Need Help solving an analytical problem

THANK YOU SO MUCH! Such a fast, beautiful and logical solution ...

Discussion stats
• 2 replies
• 298 views
• 2 likes
• 2 in conversation