How would you code a merge that will write the matches of
both to one data set, the non-matches from the left-most
data set to a second data set, and the non-matches of the
right-most data set to a third data set.
Answers were Sorted based on User's Feedback
Answer / tangyoulei
data new1 new2 new3;
merge old1 (in=one) old2 (in=two);
if one and two then output new1;
else if one and not two then output new2;
else output new3;
run;
Is This Answer Correct ? | 16 Yes | 0 No |
Answer / arun & g.n.rao
data one;
input ID$ NAME$;
datalines;
A01 SUE
A02 TOM
A05 KAY
A10 JIM
;
RUN;
DATA TWO;
INPUT ID$ AGE SEX$;
DATALINES;
A01 58 F
A02 20 M
A04 47 F
A10 11 M
;
RUN;
DATA N1 N2 N3;
MERGE ONE (IN=X) TWO (IN=Y);
BY ID;
IF X=1 AND Y=1 THEN OUTPUT N1;
IF X=1 AND Y=0 THEN OUTPUT N2;
IF X=0 AND Y=1 THEN OUTPUT N3;
RUN;
Is This Answer Correct ? | 9 Yes | 1 No |
Answer / proc sql
proc sql:
proc sql;
create table sqln1 as select one.id,name,age,sex from one
inner join two on one.id=two.id;
create table sqln2 as select one.id,name,age,sex from one
left join two on one.id=two.id where two.id is null;
create table sqln3 as select two.id,name,age,sex from one
right join two on one.id=two.id where one.id is null;
quit;
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / vinod swarna
proc sql;
create table both_match as
select *
from one
intersect
select *
from two;
create table left_non as
select *
from one
except
select *
from two;
create table right_non as
select *
from two
except
select *
from one;
quit;
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / lakshmi
data data1 data2 data3;
merge left(in-a) right(in=b);
by subjid;
if a and b then output data1;
if a and not b then output data2;
if b and not a then output data3;
run;
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / vipin choudhary
Assume the common variable in both of the old datasets
is "name".
Data One two three;
merge old(in = in1) old1(in = in2);
by name;
if in1 and in2 then output one;
if in1 then output two;
if in1 = 0 and in2 = 1 then output three;
run
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / giri
this problem over come by using the joins statements
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / m.sivakumar
proc sql;
create table sqln1 as select one.id,name,age,sex from one
inner join two on one.id=two.id;
create table sqln2 as select one.id,name,age,sex from one
left join two on one.id=two.id where two.id is null;
create table sqln3 as select coalesce(one.id,two.id)as
id,name,age,sex from one right join two on one.id=two.id
where one.id is null;
quit;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / padma
The above proc sql is not working. it is throwing up error.
Please verify. If anybody give proc sql code for the above
question that would be great.
padma
Is This Answer Correct ? | 0 Yes | 1 No |
what is the difference between nodup and nodupkey options? : Sas programming
what is sas database server? : Sas-di
DATA ABC; INPUT TDATE DATE9. AVG; CARDS; 18APR2008 150.00 19APR2008 167.00 20APR2008 123.00 21APR2008 145.00 ; RUN HOW CAN I FIND THE DIFFERENCE BETWEEN AVG OF 18APR2008 ANF 21APR2008?? IF ANY ONE GETS IT PLS TRY TO POST IT.
8 Answers Verinon Technology Solutions,
How to display duplicate observations in data?
Can you explain the process of calendar?
tell me about use of arrays in sas
Enlist the syntax rules followed in sas statements.
it will become easy if uuu provide website linkssss and list of consultanciessssss
what is the use of proc sql?
How to save the log information in a file for future use
Explain proc univariate?
How to Rename Library?