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 are the difference between ceil and floor functions in sas?
what is sas enterprise intelligence architecture? : Sas-bi
data task; input id date date9. visit; cards; 101 01jan2015 1 101 02jan2015 2 101 06jan2015 3 102 04jan2015 1 102 07jan2015 2 102 12jan2015 3 103 06jan2015 1 103 13jan2015 2 ; run; write a program to find out missing dates between visits by each subject.
What are the special input delimiters used in SAS?
how to add distinctly var variable values ex.. Data a; input var; datalines; 0 1 2 3 -1 -2 -3 ; run; adding all +ve value in one varibale n do the same for -ve too
How do you generate random samples?
Do you prefer Proc Report or Proc Tabulate? Why?
What does the trace option do?
In sas admin differentiate between roles and capabilities? : sas-grid-administration
1 new york 7,262,700 2 los angeles 3,259,340 3 philadelphia 1,642,900 how to read the above data using input statement consider the above data is in txt format externally u have to use infile and input statement.
What is auto call macro and how to create a auto call macro? What is the use of it? How to use it in sas with macros? : sas-macro
Mention common programming errors committed in sas ?