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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: