Replace missing values by looking at non-missing (and matching in two columns) values.

[MB1]

Hello everyone,

Can someone help me with the following:

I have two colums. Sometimes the value in one of them is missing. The values form unique pairs. For example: when G is in the first column, then D should be in the second. G and D can only match with eachother. (note that D can never be in column 1 and G never in column 2). I would like SAS to fill the missing spots with the right value, that becomes clear from the rows that do have a matching value in the other column.

I would like to go from this:

Column 1         Column 2

A                        B

.                         J

G                       D

F                        .

.                         C

F                        J

O                       C

O                       C

A                        .

A                        .

.                         D

.                         D

.                         B

(you can see that the pairs are: A&B, G&D, F&J and O&C)

To this:

Column 1         Column 2

A                         B

F                         J

G                        D

F                         J

O                        C

F                         J

O                        C

O                        C

A                         B

A                         B

G                         D

G                         D

A                         B

Many thanks in advance!

[Loko]

Hello,

Hash solution:

data have;
infile datalines;
input Column1  $ Column2 $;
datalines;
A B
.  J
G D
F .
.  C
F J
O C
O C
A .
A .
.  D
.  D
.  B
;
run;

data want;

set have;
if _N_=1 then 
 do;
 	declare hash c1(dataset:"have(where=(missing(Column1)=0 and missing(Column2)=0))");
	c1.definekey("Column1");
	c1.definedata("Column2");
	c1.definedone();
	declare hash c2(dataset:"have(where=(missing(Column1)=0 and missing(Column2)=0))");
	c2.definekey("Column2");
	c2.definedata("Column1");
	c2.definedone();
end;

if missing(Column1) then c2.find();
if missing(Column2) then c1.find();

run;