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

MB1 — Thu, 07 Jul 2016 09:29:33 GMT

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

A .

. 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

A B

G D

A B

Many thanks in advance!

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

Loko — Thu, 07 Jul 2016 09:40:32 GMT

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;

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

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

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