BookmarkSubscribeRSS Feed
rishabhmehra13
Calcite | Level 5

i want to join table d.p4 with dig ,Probability is the id

 

 

proc sql;
create table p1 as select a.Account_Name as client ,a.Opportunity_Owner as champ,
a.Last_Modified_Date as modified , sum(a.Total_Media_Value) as Tot_Budget,a.Deal_Comments,
 
b.Total_Media_Value as Digital_bdgt
from d.p3 as a left join dig as b
on a.Probability____= b.Probability____
group by Probability____
order by  Probability____ desc;

quit;

 

 

log

63         group by Probability____
64         order by  Probability____ desc;
ERROR: Ambiguous reference, column Probability____ is in more than one table.
ERROR: Ambiguous reference, column Probability____ is in more than one table.
5 REPLIES 5
Astounding
PROC Star

When you joined the data sets, you specified a.Probability___ or b.Probability___.  That's the right way to do it. 

 

When you get to GROUP BY or ORDER BY, you have to do the same thing.  Pick one.  When you just say Probability___, SQL doesn't know which one you mean.

rishabhmehra13
Calcite | Level 5
I am still not getting the desired output.
both the data sets have the same values of probability and other columns
too which i require but i want that the output should be all the required
columns of set 1 and only 1 column to be joined to that set 1based on
probability,i dont want repeating probabilities.

proc sql;
create table p1 as select a.Probability____ , a.Account_Name as client
,a.Opportunity_Owner as champ,
a.Last_Modified_Date as modified , sum(a.Total_Media_Value) as
Tot_Budget,a.Deal_Comments,

b.Total_Media_Value as Digital_bdgt
from d.p3 as a left join dig as b
on a.Probability____= b.Probability____
group by a.Probability____
order by a.Probability____ desc;

quit;

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Reeza
Super User

@rishabhmehra13 wrote:
 i want that the output should be all the required

columns of set 1 and only 1 column to be joined to that set 1based on
probability,i dont want repeating probabilities.



If you include variables NOT in your group by clause (or an aggregate function) in your SELECT statement they are considered distinct and are repeated for each row. If you want distinct values add them to your group by or an aggregate function as appropriate. 

 

 

Shmuel
Garnet | Level 18

better display a group of input and its output to show what is wrong 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2251 views
  • 0 likes
  • 5 in conversation