turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Studio
- /
- Problem while merging

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-26-2016 07:53 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-26-2016 08:14 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-26-2016 08:19 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-26-2016 08:37 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-26-2016 08:40 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-27-2016 02:02 AM

All variables in the select that are not the subject of a summary function need to go into the group by clause.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers