outer join

CG1 — Thu, 05 May 2016 13:11:14 GMT

Hi All,

I am getting some issue while outer join the tables. Please help

I have 2 data set (test1) and (test2). In test1 ther is 2 columns and in test2 there is 1 column only.

Now I want to put outer join on both of the tables. I want it should display all the records from test1 and where code is not matching it should blank for code. see below is my code

data test1;
input name $ code $;
datalines;
RAHUL A12345
RAJ A45678
ROC A12345
MOHAN KJ7652
SOHAN M23456
ROHAN N87654
;
run;

data test2;
input code$;
datalines;
A12345
A45678
KJ7652
KK8765
PK9876
QU6543
;
run;

proc sort data=test1;
by code;
run;

proc sort data=test2;
by code;
run;

data test3;
merge test1 (keep=_all_ in=a)
test2 (keep =code in=b);
by code;
if (a);
run;

I want the output which is displaying in below proc sql.Please help how I can get both the code values in data step

proc sql;
select a.name,a.code,b.code
from test1 as a left outer join test2 as b
on a.code=b.code;
quit

Re: outer join

Doc_Duke — Thu, 05 May 2016 13:46:58 GMT

Are you looking for a report or a SAS dataset? The DATA step is generating a SAS dataset and the SQL is just generating a report. If you tried to create a dataset from the SQL, you would need to rename test2.code to something else to get two 'code' columns. You could accomplish the same thing in test2 with an assignment statement (essentially duplicating test2.code with a different name).

topic Re: outer join in SAS Programming

outer join

Re: outer join