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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.