Solved: Re: ambigious Reference to mbr_id

Rsadiq · Posted 10-31-2017 07:36 AM

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

Kurt_Bremser · Posted 10-31-2017 08:47 AM

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
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

View solution in original post

Kurt_Bremser · Posted 10-31-2017 08:25 AM

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
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Rsadiq · Posted 10-31-2017 08:32 AM

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