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
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 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.
better display a group of input and its output to show what is wrong
All variables in the select that are not the subject of a summary function need to go into the group by clause.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.