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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.