BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
atul_desh
Quartz | Level 8

Yes I know I should write as below : 

"on a.ManagerID=b.ID;"

but when i'm writting as below how i'm getting the output can someone please explain logic how output calculated 

 

data ds1;
input Name $ ID ManagerID;
cards;
Smith 123 456
Robert 456 .
William 222 456
Daniel 777 222
Cook 383 222
;
run;

proc sql;
select a.*, b.Name as Manager_name from ds1 as a left join ds1 as b
on b.ManagerID=a.ID;
quit;

 

 

Output : 

 

SAS Output

Name ID ManagerID Manager_name
Smith 123 456  
William 222 456 Cook
William 222 456 Daniel
Cook 383 222  
Robert 456 . Smith
Robert 456 . William
Daniel 777 222  

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Which of the output lines surprise you? All follow your condition.

The first, fourth and fifth records of your datasets are taken as is because there are no matching ManagerID's.

The second and third each find two matches, and the names from those appear in Manager_name.

 

Unless an explicit order is specified, the order of the output is determined by the internal mechanism of SQL.

Which seems to take the key of the left side in the join for the order.

View solution in original post

1 REPLY 1
Kurt_Bremser
Super User

Which of the output lines surprise you? All follow your condition.

The first, fourth and fifth records of your datasets are taken as is because there are no matching ManagerID's.

The second and third each find two matches, and the names from those appear in Manager_name.

 

Unless an explicit order is specified, the order of the output is determined by the internal mechanism of SQL.

Which seems to take the key of the left side in the join for the order.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 822 views
  • 0 likes
  • 2 in conversation