Desktop productivity for business analysts and programmers

ambigious Reference to mbr_id

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

ambigious Reference to mbr_id

Hello

 

I am getting an error I don't see a lot of the time

 


ERROR: Ambiguous reference, column mbr_id is in more than one table.
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT
         clause nor the optional HAVING clause of the associated table-expression referenced a
         summary function.

 

My code is as below

 

proc sql ;
       create table pharmacy_consults1  as
        select distinct
                      
                      a.ACTV_STRT_DTTM
                      ,case when a.ACTV_TYP_ID in ('121') and a.RSLV_OTCOME_TYP_ID in ('369') then 1
                         else 0    end                       format=6.           as REFERRALS
                         ,a.ACTVCREATDT
                      from sasdata.activities_mbr a
                      left join sasdata.falls_mbr_flg_20171030 b
                      on a.mbr_id=b.mbr_id
                    where (ACTVSTRT_DTTM is not null and ACTV_STRT_DTTM between "&beg_dt."d and "&end_dt."d) 
                         

 

Can someone help, on why mbr_id is causing this error?

 

Thank you

 

Rida


Accepted Solutions
Solution
‎11-03-2017 04:10 PM
Super User
Posts: 10,535

Re: ambigious Reference to mbr_id

The location of the error messages tells us that the offending SQL step happens near the end of

%include "&codepath./cp_activities.sas" ;

so you have to check there.

ERROR: Ambiguous reference, column mbr_id is in more than one table.
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT
         clause nor the optional HAVING clause of the associated table-expression referenced a
         summary function.
ERROR: The following columns were not found in the contributing tables: ACTVSTRT_DTTM.

also tells us you should be looking for SQL code that uses ACTVSTRT_DTTM, has a group by clause, but no summary function (like sum(), max(), min()). There you will also find a reference to mbr_id without the necessary table alias.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,535

Re: ambigious Reference to mbr_id

[ Edited ]

Please post the log of the whole proc sql step, using the {i} icon to preserve formatting.

Since your posted code does not contain a group by, it can't be the offending code.

Or you omitted the group by clause when posting, and it contains the ambiguous reference.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 36

Re: ambigious Reference to mbr_id

Posted in reply to KurtBremser

1                                             Full logg          

 

 

 

   The SAS System                             20:45 Monday, October 30, 2017

NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.
NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M3)
      Licensed to OPTUM - ODM - OI GRID - PROD - NODE 2, Site 70017167.
NOTE: This session is executing on the AIX 7.1 (AIX 64) platform.

 

NOTE: Updated analytical products:
     
      SAS/STAT 14.1
      SAS/QC 14.1

NOTE: Additional host information:

IBM AIX AIX 64 1 7 00FA32854C00

You are running SAS 9. Some SAS 8 files will be automatically converted
by the V9 engine; others are incompatible.  Please see
http://support.sas.com/rnd/migration/planning/platform/64bit.html

PROC MIGRATE will preserve current SAS file attributes and is
recommended for converting all your SAS libraries from any
SAS 8 release to SAS 9.  For details and examples, please see
http://support.sas.com/rnd/migration/index.html


This message is contained in the SAS news file, and is presented upon
initialization.  Edit the file "news" in the "misc/base" directory to
display site-specific news and information in the program log.
The command line option "-nonews" will prevent this display.

 


NOTE: SAS initialization used:
      real time           0.09 seconds
      cpu time            0.01 seconds
     
NOTE: Libref SASDATA successfully assigned from logical server.
NOTE: Libref stpsamp successfully assigned from logical server.

NOTE: AUTOEXEC processing beginning; file is /hasasaa/files/shared/hasasaa_autoexec.sas.

WARNING: Libref OIATMPLT is not assigned.
NOTE: Libref OIATMPLT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/shared/hasasaa
Current ODS PATH list is:

1. SASUSER.TEMPLAT(UPDATE)
2. OIATMPLT.TEMPLAT(READ)
3. SASHELP.TMPLMST(READ)
thisprog -----> /hasasaa/reporting/projects/adhoc_pm_sadiq/code/driver_mcp_monthly.sas

NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
2                                                          The SAS System                             20:45 Monday, October 30, 2017

      cpu time            0.01 seconds
     

PROGRAM   ----> driver_mcp_monthly.sas
PROJPATH  ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/
PROJNAME  ----> adhoc_pm_sadiq
JOBNAME   ----> driver_mcp_monthly
ISPROJECT ----> projects
NEWAUTOS  ----> '/hasasaa/reporting/projects/adhoc_pm_sadiq/macros'
DTSTAMP     ----> 20171030-20452670
IDPWDPATH = /hasasaa/idpwd
SYSUSER = rsadiq

NOTE: PROCEDURE PWENCODE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

MPRINT(SCHEMA_CONNECT):  ;
MPRINT(SCHEMA_CONNECT):   data _null_;
MPRINT(SCHEMA_CONNECT):   infile pwfile obs=1 length=l;
MPRINT(SCHEMA_CONNECT):   input @;
MPRINT(SCHEMA_CONNECT):   input @1 line $varying1024. l;
MPRINT(SCHEMA_CONNECT):   call symput('crmextro_pass',substr(line,1,l));
MPRINT(SCHEMA_CONNECT):   run;

NOTE: The infile PWFILE is:
      Filename=/hasasaa/idpwd/rsadiq/pass.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=30Oct2017:20:45:26,
      File Size (bytes)=41

NOTE: 1 record was read from the infile PWFILE.
      The minimum record length was 40.
      The maximum record length was 40.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
     

MLOGIC(SCHEMA_CONNECT):  %IF condition %sysfunc(fileexist(&Idpwd./&sysuserid./autolib.sas)) is FALSE
MPRINT(SCHEMA_CONNECT):   libname crmtemp teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971"
server="udwprod" schema=crmtemp ;
NOTE: Libref CRMTEMP was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname ecroext teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971"
server="udwprod" schema=ecroext ;
NOTE: Libref ECROEXT was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname ng teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971" server="udwprod"
schema=ng ;
NOTE: Libref NG was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname symm8 teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971" server="udwprod"
3                                                          The SAS System                             20:45 Monday, October 30, 2017

schema=symm8 ;
NOTE: Libref SYMM8 was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname cms teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971" server="udwprod"
schema=cms ;
NOTE: Libref CMS was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname isdw sqlsvr dsn="isdw_prd_iwa" user="rsadiq" pwd="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971"
schema=compas;
NOTE: Libref ISDW was successfully assigned as follows:
      Engine:        SQLSVR
      Physical Name: isdw_prd_iwa
MLOGIC(SCHEMA_CONNECT):  %PUT CRM connection complete
CRM connection complete
MLOGIC(SCHEMA_CONNECT):  %IF condition %sysfunc(fileexist(&Idpwd./&sysuserid./oim.txt)) is TRUE
MPRINT(SCHEMA_CONNECT):   data _null_ ;
MPRINT(SCHEMA_CONNECT):   infile "/hasasaa/idpwd/rsadiq/oim.txt" recfm=V truncover ;
MPRINT(SCHEMA_CONNECT):   input OIMID : $13. OIMPW : $35.;
MPRINT(SCHEMA_CONNECT):   call symputx ('OIMID',strip(left(OIMID)));
MPRINT(SCHEMA_CONNECT):   call symputx ('OIMPW',strip(left(OIMPW)));
MPRINT(SCHEMA_CONNECT):   run;

NOTE: The infile "/hasasaa/idpwd/rsadiq/oim.txt" is:
      Filename=/hasasaa/idpwd/rsadiq/oim.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=16Aug2017:07:59:38,
      File Size (bytes)=48

NOTE: 2 records were read from the infile "/hasasaa/idpwd/rsadiq/oim.txt".
      The minimum record length was 21.
      The maximum record length was 23.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

MPRINT(SCHEMA_CONNECT):   options obs=max nomprint nomlogic
NOTE: Libref STG_HSR was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: urnprr
NOTE: Libref STG_CARE was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: urnprr
STG_HSR connection complete

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     


NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     
4                                                          The SAS System                             20:45 Monday, October 30, 2017


NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/sasout" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/output" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/input" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/qa" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/refdata" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/sasdata" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/macros" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/code" exists
NOTE: The directory "/hasasaa/shared/hasasaa" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/testing" exists
NOTE: The directory "/hasasaa/reporting/projects/adhoc_pm_sadiq/archive" exists
NOTE: The directory "/hasasaa/common/refdata" exists
NOTE: Libref SASOUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/sasout
NOTE: Libref OUTPUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/output
NOTE: Libref INPUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/input
NOTE: Libref QA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/qa
NOTE: Libref REFDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/refdata
NOTE: Libref SASDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/sasdata
NOTE: Libref MACROS was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/macros
NOTE: Libref CODE was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/code
NOTE: Libref SHARED refers to the same physical library as OIATMPLT.
NOTE: Libref SHARED was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/shared/hasasaa
NOTE: Libref TEST was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/testing
NOTE: Libref ARCHIVE was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/archive
NOTE: Libref COMREF was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/common/refdata
SASOUTPATH  ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/sasout
OUTPUTPATH  ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/output
INPUTPATH   ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/input
QAPATH      ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/qa
REFDATAPATH ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/refdata
SASDATAPATH ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/sasdata
MACROPATH   ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/macros
CODEPATH    ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/code
5                                                          The SAS System                             20:45 Monday, October 30, 2017

ARCHIVEPATH ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/archive
LOGFILE     ----> '/hasasaa/reporting/projects/adhoc_pm_sadiq/sasout/driver_mcp_monthly_20171030-20452670.log'
SHAREDPATH  ----> /hasasaa/shared/hasasaa
TESTPATH    ----> /hasasaa/reporting/projects/adhoc_pm_sadiq/testing
NASOUTPUTPATH ----> /hasasaa/oia/oiaprod/Data/output
NASINPUTPATH ----> /hasasaa/oia/oiaprod/Data/input
COMMONREFPATH ----> /hasasaa/common/refdata
COMMONREFPATH=/hasasaa/common/refdata
GLOBAL ARCHIVEPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/archive
GLOBAL CODEPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/code
GLOBAL COMMONCODEPATH /hasasaa/common/code
GLOBAL COMMONMACROPATH /hasasaa/common/macros
GLOBAL COMMONREFPATH /hasasaa/common/refdata
GLOBAL CRMEXTRO_PASS {SASENC}BA7B9D0621DAD7594C80A0DD4A540971
GLOBAL CRMEXTRO_PATH
GLOBAL CRMEXTRO_USER rsadiq
GLOBAL CRMRO_PASS
GLOBAL CRMRO_PATH
GLOBAL CRMRO_USER
GLOBAL DTSTAMP 20171030-20452670
GLOBAL INPUTPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/input
GLOBAL ISPROJECT projects
GLOBAL JOBNAME driver_mcp_monthly
GLOBAL LOGFILE '/hasasaa/reporting/projects/adhoc_pm_sadiq/sasout/driver_mcp_monthly_20171030-20452670.log'
GLOBAL LOGOUT
GLOBAL MACROPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/macros
GLOBAL NASINPUTPATH /hasasaa/oia/oiaprod/Data/input
GLOBAL NASOUTPUTPATH /hasasaa/oia/oiaprod/Data/output
GLOBAL NEWAUTOS '/hasasaa/reporting/projects/adhoc_pm_sadiq/macros'
GLOBAL OIMID UHG_001200214
GLOBAL OIMPW Cooly6*u
GLOBAL OUTPUTPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/output
GLOBAL PROGRAM driver_mcp_monthly.sas
GLOBAL PROJNAME adhoc_pm_sadiq
GLOBAL PROJPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/
GLOBAL QAPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/qa
GLOBAL QTR
GLOBAL REFDATAPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/refdata
GLOBAL SASDATAPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/sasdata
GLOBAL SASOUTPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/sasout
GLOBAL SHAREDPATH /hasasaa/shared/hasasaa
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
GLOBAL TEMP_TBL TEMP_mmcginn
GLOBAL TESTPATH /hasasaa/reporting/projects/adhoc_pm_sadiq/testing
GLOBAL THISPROG /hasasaa/reporting/projects/adhoc_pm_sadiq/code/driver_mcp_monthly.sas
GLOBAL UDW_SERVER udwprod
GLOBAL _PWENCODE {SASENC}BA7B9D0621DAD7594C80A0DD4A540971
AUTOMATIC AFDSID 0
AUTOMATIC AFDSNAME
AUTOMATIC AFLIB
AUTOMATIC AFSTR1
AUTOMATIC AFSTR2
AUTOMATIC FSPBDV
AUTOMATIC SYSADDRBITS 64
AUTOMATIC SYSBUFFR
AUTOMATIC SYSCC 0
AUTOMATIC SYSCHARWIDTH 1
6                                                          The SAS System                             20:45 Monday, October 30, 2017

AUTOMATIC SYSCMD
AUTOMATIC SYSDATASTEPPHASE
AUTOMATIC SYSDATE 30OCT17
AUTOMATIC SYSDATE9 30OCT2017
AUTOMATIC SYSDAY Monday
AUTOMATIC SYSDEVIC
AUTOMATIC SYSDMG 0
AUTOMATIC SYSDSN         _NULL_                         
AUTOMATIC SYSENCODING latin1
AUTOMATIC SYSENDIAN BIG
AUTOMATIC SYSENV BACK
AUTOMATIC SYSERR 0
AUTOMATIC SYSERRORTEXT
AUTOMATIC SYSFILRC 0
AUTOMATIC SYSHOSTINFOLONG IBM AIX AIX 64 1 7 00FA32854C00
AUTOMATIC SYSHOSTNAME apspp0540
AUTOMATIC SYSINDEX 17
AUTOMATIC SYSINFO 0
AUTOMATIC SYSJOBID 29884428
AUTOMATIC SYSLAST _NULL_
AUTOMATIC SYSLCKRC 0
AUTOMATIC SYSLIBRC 0
AUTOMATIC SYSLOGAPPLNAME
AUTOMATIC SYSMACRONAME DO_PROJECT
AUTOMATIC SYSMAXLONG 9007199254740992
AUTOMATIC SYSMENV
AUTOMATIC SYSMSG
AUTOMATIC SYSNCPU 4                  
AUTOMATIC SYSNOBS 0
AUTOMATIC SYSODSESCAPECHAR 
AUTOMATIC SYSODSGRAPHICS 0
AUTOMATIC SYSODSPATH  SASUSER.TEMPLAT(UPDATE) OIATMPLT.TEMPLAT(READ) SASHELP.TMPLMST(READ)
AUTOMATIC SYSPARM
AUTOMATIC SYSPRINTTOLOG
AUTOMATIC SYSPRINTTOLIST
AUTOMATIC SYSPROCESSID 41DB31E1B9A14BA14018000000000000
AUTOMATIC SYSPROCESSMODE SAS Batch Mode
AUTOMATIC SYSPROCESSNAME Program /hasasaa/reporting/projects/adhoc_pm_sadiq/code/driver_mcp_monthly.sas
AUTOMATIC SYSPROCNAME
AUTOMATIC SYSRC 0
AUTOMATIC SYSSCP AIX 64
AUTOMATIC SYSSCPL AIX
AUTOMATIC SYSSITE 70017167
AUTOMATIC SYSSIZEOFLONG 8
AUTOMATIC SYSSIZEOFPTR 8
AUTOMATIC SYSSIZEOFUNICODE 4
AUTOMATIC SYSSTARTID
AUTOMATIC SYSSTARTNAME
AUTOMATIC SYSTCPIPHOSTNAME apspp0540
AUTOMATIC SYSTIME 20:45
AUTOMATIC SYSTIMEZONE
AUTOMATIC SYSTIMEZONEIDENT
AUTOMATIC SYSTIMEZONEOFFSET -18000
AUTOMATIC SYSUSERID rsadiq
AUTOMATIC SYSVER 9.4    
AUTOMATIC SYSVLONG 9.04.01M3P062415
AUTOMATIC SYSVLONG4 9.04.01M3P06242015
AUTOMATIC SYSWARNINGTEXT Libref OIATMPLT is not assigned.
7                                                          The SAS System                             20:45 Monday, October 30, 2017

MACRO CALLED: addautos v1.1
currautos = ('/hasasaa/reporting/projects/adhoc_pm_sadiq/macros' '/hasasaa/shared/hasasaa' '/hasasaa/files/shared'
'/hasasaa/shared/files'                     '/hasasaa/shared/sas' '!SASROOT/sasautos')

NOTE: AUTOEXEC processing completed.

1          options mprint mlogic symbolgen compress=yes spool
2             sasautos=(/*'/hasasaa/reporting/CAHPS/monthly/reporting/macros'*/
3                   '/hasasaa/shared/hasasaa' '/hasasaa/files/shared' 'hasasaa/shared/sas' '!SASROOT/sasautos'  ) ;
4         
5           %let sasdata = /hasasaa/reporting/projects/adhoc_pm_sadiq/sasdata ;
6          %let outputpath = /hasasaa/reporting/projects/adhoc_pm_sadiq/output ;
7          %let macropath = /hasasaa/reporting/projects/adhoc_pm_sadiq/macros ;
8          %let codepath = /hasasaa/reporting/projects/adhoc_pm_sadiq/code ;
9          %let logpath = /hasasaa/reporting/projects/adhoc_pm_sadiq/sasout ;
10        
11        
12        
13         libname macpath "&macropath." ;
SYMBOLGEN:  Macro variable MACROPATH resolves to /hasasaa/reporting/projects/adhoc_pm_sadiq/macros
NOTE: Libref MACPATH refers to the same physical library as MACROS.
NOTE: Libref MACPATH was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/macros
14         libname outpath "&outputpath." ;
SYMBOLGEN:  Macro variable OUTPUTPATH resolves to /hasasaa/reporting/projects/adhoc_pm_sadiq/output
NOTE: Libref OUTPATH refers to the same physical library as OUTPUT.
NOTE: Libref OUTPATH was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_sadiq/output
15         libname tefdata "/hasasaa/reporting/projects/mcp_reporting/sasdata/common";
NOTE: Libref TEFDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/mcp_reporting/sasdata/common
16        
17         %include  "&macropath./parms_common.sas" ;
SYMBOLGEN:  Macro variable MACROPATH resolves to /hasasaa/reporting/projects/adhoc_pm_sadiq/macros

NOTE: The infile "/hasasaa/idpwd/rsadiq/oim.txt" is:
      Filename=/hasasaa/idpwd/rsadiq/oim.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=16Aug2017:07:59:38,
      File Size (bytes)=48

NOTE: 2 records were read from the infile "/hasasaa/idpwd/rsadiq/oim.txt".
      The minimum record length was 21.
      The maximum record length was 23.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

8                                          The SAS System             20:45 Monday, October 30, 2017


NOTE: The infile "/hasasaa/idpwd/rsadiq/oim2.txt" is:
      Filename=/hasasaa/idpwd/rsadiq/oim2.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=22Jun2017:07:40:34,
      File Size (bytes)=23

NOTE: 1 record was read from the infile "/hasasaa/idpwd/rsadiq/oim2.txt".
      The minimum record length was 23.
      The maximum record length was 23.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
     

NOTE: Libref STG_HSR was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: urnprr
NOTE: Libref STG_CARE was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: urnprr1
401             %include  "&macropath./parms_monthly.sas" ;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

Begheddte = "01AUG2016"D
Begqtrdte = "01AUG2016"D
Endqtrdte = "31JUL2017"D
Crmmbeg = 201706
Crmbeg = 201706
Crmend = 201706
Yyyy = 2017
Mm2 = 06
Processdt = 062017
Monprcbeg = "01AUG2016"D
Monprcend = "31JUL2017"D
Processbegdt = 01Aug2016
Processenddt = 31JUL2017
Month = Aug
Yr = 2017
600             %include "/hasasaa/reporting/projects/adhoc_pm_wu/code/FP001_parm.sas";

NOTE: PROCEDURE PWENCODE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     


NOTE: The infile PWFILE is:
9                                          The SAS System             20:45 Monday, October 30, 2017

      Filename=/hasasaa/idpwd/rsadiq/pass.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=30Oct2017:20:45:29,
      File Size (bytes)=41

NOTE: 1 record was read from the infile PWFILE.
      The minimum record length was 40.
      The maximum record length was 40.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     


NOTE: The infile "/hasasaa/idpwd/rsadiq/oim.txt" is:
      Filename=/hasasaa/idpwd/rsadiq/oim.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=16Aug2017:07:59:38,
      File Size (bytes)=48

NOTE: 2 records were read from the infile "/hasasaa/idpwd/rsadiq/oim.txt".
      The minimum record length was 21.
      The maximum record length was 23.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

NOTE: Libref INPUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_wu/input
NOTE: Libref SASDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_wu/sasdata
NOTE: Libref OUTPUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_wu/output
NOTE: Libref REFDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_wu/refdata
NOTE: Libref LIBREFZ was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/common/zipdemog
NOTE: Libref REFDATAM was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/projects/adhoc_pm_mcginn/refdata
NOTE: Libref INDATA1 refers to the same physical library as COMREF.
NOTE: Libref INDATA1 was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/common/refdata
NOTE: Libref STG_HSR was successfully assigned as follows:
10                                         The SAS System             20:45 Monday, October 30, 2017

      Engine:        ORACLE
      Physical Name: urnprr
NOTE: Libref COMPAS was successfully assigned as follows:
      Engine:        SQLSVR
      Physical Name: isdw_prd_iwa
MLOGIC(SETUP_DT):  Beginning execution.
MLOGIC(SETUP_DT):  %LET (variable name is NHLDAT)
MLOGIC(SETUP_DT):  %LET (variable name is MONS_BACK)
MPRINT(SETUP_DT):   data _null_ ;
MPRINT(SETUP_DT):   today_dt=put(today(),date9.) ;
MPRINT(SETUP_DT):   run_dt = put(today(),yymmddn8.) ;
MPRINT(SETUP_DT):   curnt = today() ;
MPRINT(SETUP_DT):   format curnt yymmdd10. ;
MPRINT(SETUP_DT):   cov_end = compress("'"||today_dt||"'"||"d") ;
MPRINT(SETUP_DT):   sas_rpt_beg_dt=intnx('MON',curnt,-1,'BEGINNING');
MPRINT(SETUP_DT):   sas_rpt_end_dt=intnx('MON',curnt,-1,'END');
MPRINT(SETUP_DT):   rptmon =
compress(put(year(sas_rpt_beg_dt),z4.)||put(month(sas_rpt_beg_dt),z2.)) ;
MPRINT(SETUP_DT):   call symputx('cov_end',cov_end) ;
MPRINT(SETUP_DT):   call symputx('run_dt',run_dt) ;
MPRINT(SETUP_DT):   CALL SYMPUT("rpt_beg_dt","'"||PUT(INTNX('MONTH',"30OCT17"D,
(-0),'B'),date9.)||"'d");
MPRINT(SETUP_DT):   CALL SYMPUT("rpt_end_dt","'"||PUT(INTNX('MONTH',"30OCT17"D,
(-0),'E'),date9.)||"'d");
MPRINT(SETUP_DT):   call symputx('rptmon',rptmon) ;
MPRINT(SETUP_DT):   * CALL SYMPUT("beg_dt","'"||PUT(INTNX('Week',"&SYSDATE"D,-1,'B'),date9.)||"'d");
MPRINT(SETUP_DT):   run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

MLOGIC(SETUP_DT):  Ending execution.
cov_end = '30OCT2017'd
ST_CD_lst = 'MD', 'MO'
run_dt = 20171030
beg_dt = '01Sep2017'd
rpt_beg_dt = '01OCT2017'd
rpt_end_dt = '31OCT2017'd
rptmon = 201709
MLOGIC(CRMPARMS):  Beginning execution.
MLOGIC(CRMPARMS):  %GLOBAL  CRMCLIENTKY OPTSEG
MLOGIC(CRMPARMS):  %LET (variable name is CRMCLIENTKY)
MLOGIC(CRMPARMS):  %LET (variable name is OPTSEG)
MLOGIC(CRMPARMS):  %LET (variable name is CLSS_DESC)
MLOGIC(CRMPARMS):  Ending execution.
801           libname refdata "/hasasaa/common/refdata";
NOTE: Libref REFDATA refers to the same physical library as INDATA1.
NOTE: Libref REFDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/common/refdata
802       
11                                         The SAS System             20:45 Monday, October 30, 2017

803       
804       
805             %Macro Monthly_mcp;
806       
807             %global Prj ;
808       
809           %let Prj = monthly ;
810       
811          %include "&codepath./cp_activities.sas" ;
812           *%include "&codepath./cp_assignments2.sas" ;
813          * %include "&codepath./Assignments_cp3.sas";
814           * %include "&codepath./Depression_mcp_ons.sas" ;
815        *%include "&codepath./cp_nurse_case_manager.sas";
816       
817       
818       
819          %Mend Monthly_mcp;
820          %Monthly_mcp;
MLOGIC(MONTHLY_MCP):  Beginning execution.
MLOGIC(MONTHLY_MCP):  %GLOBAL  PRJ
MLOGIC(MONTHLY_MCP):  %LET (variable name is PRJ)
MPRINT(MONTHLY_MCP):   options nosymbolgen nomprint nomlogic

NOTE: There were 29024 observations read from the data set REFDATA.EVER_ENGAGED_MCP.
      WHERE (ENGAGEDBEGDT<='31JUL2017'D) and (ENGAGEDENDDT>='01AUG2016'D);
NOTE: The data set WORK.ICUE_ACTIVITIES_FALL has 28994 observations and 11 variables.
NOTE: Compressing data set WORK.ICUE_ACTIVITIES_FALL decreased size by 78.18 percent.
      Compressed is 12 pages; un-compressed would require 55 pages.
NOTE: DATA statement used (Total process time):
      real time           0.09 seconds
      cpu time            0.05 seconds
     


NOTE: There were 28994 observations read from the data set WORK.ICUE_ACTIVITIES_FALL.
      WHERE MBR_ID not = .;
NOTE: SAS sort was used.
NOTE: 70 observations with duplicate key values were deleted.
NOTE: The data set WORK.ICUE_ACTIVITIES_FALL has 28924 observations and 11 variables.
NOTE: Compressing data set WORK.ICUE_ACTIVITIES_FALL decreased size by 77.78 percent.
      Compressed is 12 pages; un-compressed would require 54 pages.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.10 seconds
      cpu time            0.06 seconds
     


NOTE: The file WORK.TMP1 (memtype=DATA) was not found, but appears on a DELETE statement.
NOTE: The file WORK.TMP2 (memtype=DATA) was not found, but appears on a DELETE statement.
NOTE: The file WORK.TMP3 (memtype=DATA) was not found, but appears on a DELETE statement.

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.02 seconds
12                                         The SAS System             20:45 Monday, October 30, 2017

      cpu time            0.01 seconds
     


NOTE: The file WORK.ACTIVITIES (memtype=DATA) was not found, but appears on a DELETE statement.

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
13                                         The SAS System             20:45 Monday, October 30, 2017

      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
     

14                                         The SAS System             20:45 Monday, October 30, 2017

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
     

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
15                                         The SAS System             20:45 Monday, October 30, 2017

     

NOTE: SAS threaded sort was used.
NOTE: Compressing data set WORK.ACTIVITIES increased size by 4.53 percent.
      Compressed is 277 pages; un-compressed would require 265 pages.
NOTE: Table WORK.ACTIVITIES created, with 1082777 rows and 9 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           30:45.06
      cpu time            26.79 seconds
     

NOTE: SAS threaded sort was used.
NOTE: Compressing data set SASDATA.ACTIVITIES_AUG_2017 decreased size by 92.88 percent.
      Compressed is 602 pages; un-compressed would require 8451 pages.
NOTE: Table SASDATA.ACTIVITIES_AUG_2017 created, with 1081657 rows and 24 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           16.81 seconds
      cpu time            12.20 seconds
     

NOTE: The file WORK._ALL_ (memtype=DATA) was not found, but appears on a DELETE statement.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
     

NOTE: SAS threaded sort was used.
NOTE: Compressing data set WORK.ACTIVITIES_MBR decreased size by 86.05 percent.
      Compressed is 522 pages; un-compressed would require 3741 pages.
NOTE: Table WORK.ACTIVITIES_MBR created, with 1054689 rows and 12 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           7.19 seconds
      cpu time            5.26 seconds
     

ERROR: Ambiguous reference, column mbr_id is in more than one table.
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT
         clause nor the optional HAVING clause of the associated table-expression referenced a
         summary function.
ERROR: The following columns were not found in the contributing tables: ACTVSTRT_DTTM.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
     


ERROR: Errors printed on page 15.

16                                         The SAS System             20:45 Monday, October 30, 2017

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
      real time           31:14.35
      cpu time            46.33 seconds
     

Solution
‎11-03-2017 04:10 PM
Super User
Posts: 10,535

Re: ambigious Reference to mbr_id

The location of the error messages tells us that the offending SQL step happens near the end of

%include "&codepath./cp_activities.sas" ;

so you have to check there.

ERROR: Ambiguous reference, column mbr_id is in more than one table.
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT
         clause nor the optional HAVING clause of the associated table-expression referenced a
         summary function.
ERROR: The following columns were not found in the contributing tables: ACTVSTRT_DTTM.

also tells us you should be looking for SQL code that uses ACTVSTRT_DTTM, has a group by clause, but no summary function (like sum(), max(), min()). There you will also find a reference to mbr_id without the necessary table alias.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,813

Re: ambigious Reference to mbr_id

The error, as is always the case, tells you the problem:

ERROR: Ambiguous reference, column mbr_id is in more than one table.

The variable mbr_id appears in more than one of your datasets.  However I cannot debug this as the code you post would not generate that log output.  There is no group by for instance.  Post the code exactly as it appears, and post the log of the relevant section, and show the error.  Its likely you are missing an alias of a use of mbr_id, i.e.

select mbr_id

Rather than

select a.mbr_id

But I cannot tell as the code posted is not the same.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 190 views
  • 0 likes
  • 3 in conversation