BookmarkSubscribeRSS Feed
CG1
Calcite | Level 5 CG1
Calcite | Level 5

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

1 REPLY 1
Doc_Duke
Rhodochrosite | Level 12

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).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 683 views
  • 0 likes
  • 2 in conversation