Date:         Thu, 9 Dec 2010 09:37:39 -0500
Reply-To:     Arthur Tabachneck <art297@ROGERS.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@ROGERS.COM>
Subject:      Re: problem with inner join in proc sql
Comments: To: Lorna Simon <Lorna.Simon@UMASSMED.EDU>

Lorna,

Hard to tell (for me) without some example data, but I did notice one
possible error in the step just before you say you get the errors:

proc sql;
create table vwsitesxparticipants as
        SELECT  vwsites.siteid,
                                vwsites,panelid,
                                participantdata2.participantid,
                                participantdata2.panel_role
                FROM    vwsites
                inner join participantdata2 as p
        on      vwsites.siteid = p.siteid and
                        vwsites.panelid = p.panelid

Should that second item on the select statement have read
vwsites.panelid, rather than vwsites,panelid,  ???

Art
--------
On Thu, 9 Dec 2010 09:27:21 -0500, Simon, Lorna <Lorna.Simon@UMASSMED.EDU>
wrote:

>This is probably something that many people on this list will see right
away, but bear with me, I'm relatively new to proc sql. I'm trying to do an
inner join of 2 tables I created in previous steps and I'm getting the
following error when I try to join vwsites and participantdata2 to create
the table vwsitesxparticipants:
>
>ERROR: Ambiguous reference, column panelid is in more than one table.
>ERROR: The following columns were not found in the contributing tables:
vwsites.
>
>My code follows:
>
>/* create the sites by participant file = vwturnswords */
>proc sql;
>create table vwturnswords as
>select  siteid, panelid, participantid,
>                count(speaking_turn) as turncount,
>                        sum(wordcount) as words
>         from irbstudy.meeting_data_final11_23_10
>     group by siteid, panelid, participantid
>         order by siteid, panelid, participantid
>;
>
>/* create the view vwsites */
>create table vwsites as
>        select distinct siteid, panelid
>        from irbstudy.meeting_data_final11_23_10
>;
>quit;
>
>/* the inner join of sites x participants, vwsitesxparticipants */
>proc sql;
>create table vwsitesxparticipants as
>        SELECT  vwsites.siteid,
>                                vwsites,panelid,
>                                participantdata2.participantid,
>                                participantdata2.panel_role
>                FROM    vwsites
>                inner join participantdata2 as p
>        on      vwsites.siteid = p.siteid and
>                        vwsites.panelid = p.panelid
>
>NOTE: error message occurs here
>;
>/* the query WhoSpeaks */
>create table meetingsXsitepanel as
>SELECT  vwsitesxparticipants.siteid,
>                vwsitesxparticipants.panelid,
>                vwsitesxparticipants.participantid,
>                vwsitesxparticipants.panel_role,
>                vwturnswords.turncount,
>                vwturnswords.words
>FROM    vwsitesxparticipants
>left join vwturnswords
>on              vwsitesxparticipants.siteid = vwturnswords.siteid AND
>                vwsitesxparticipants.panelid = vwturnswords.panelid and
>                vwsitesxparticipants.participantid =
vwturnswords.participantid
>;
>quit;
>
>Any help from the SAS gurus on this list would be much appreciated. Thanks.