I am not sure where to post this question. If this is not the forum, could you tell me which one.
I am using Proc SQL. I have the following code. I have remmed out the sections that are giving me the error with /* */. When I put these sections back into the code, I get "Correlated Reference to Column GP_N is not contained within a subquery.
I know it has something to do with the syntax but I am not sure how to correct it. I remmed out the code relating to table GroupCLH since that is what is giving me the problem. I am doing outer joins and I have a one to one join for the GroupCLH table. It doesn't like where I put it in the from statement.
The code is:
PROC SQL;
create table LINDA.LifestyleReturns as
Select
distinct
VIEWLIFESTYLEREPORT.GP_ID,
VIEWLIFESTYLEREPORT.CL_N,
VIEWLIFESTYLEREPORT.GP_N,
VIEWLIFESTYLEREPORT.GP_NM,
VIEWLIFESTYLEREPORT.GP_EFF_FRM_DT,
VIEWLIFESTYLEREPORT.GP_EFF_TO_DT,
VIEWLIFESTYLEREPORT.LSR_CL_IN,
VIEWLIFESTYLEREPORT.COLSV_DESC_T,
VIEWLIFESTYLEREPORT.LSR_ATP_DESC,
VIEWLIFESTYLEREPORT.LSR_PART_DESC,
VIEWLIFESTYLEREPORT.LSR_RWD_METH,
VIEWLIFESTYLEREPORT.LSR_OTCM_PART,
VIEWLIFESTYLEGROUPS.GROUP_NUMBER,
VIEWLIFESTYLEGROUPS.LSR_EFF_FRM_DT,
VIEWLIFESTYLEGROUPS.LSR_EFF_TO_DT,
VIEWLIFESTYLEGROUPS.CONTRACT_BEGIN_DT,
VIEWLIFESTYLEGROUPS.CONTRACT_END_DT,
VIEWLIFESTYLEGROUPS.GROUP_MKT_STUS_C,
/*GROUPCLH.CL_ID,
GROUPCLH.CL_ANV_M,
GROUPCLH.PARTNERPLAN,
GROUPCLH.EPLATFORMIND,*/
CLIENTMGR.CL_ID,
CLIENTMGR.SD_N,
CLIENTMGR.SD_NM,
CLIENTMGR.SSSR_N,
CLIENTMGR.EM_LAST_NM,
CLIENTMGR.CSG,
ASM.CL_ID,
ASM.SSSR_N,
ASM.EM_LAST_NM,
VIEWLIFESTYLEWORKLOAD.GROUP_ID,
VIEWLIFESTYLEWORKLOAD.FA_TOTAL_CONTRACTS,
VIEWLIFESTYLEWORKLOAD.PRODUCT_DESCRIPTION,
GENERALADDRESS.CLID,
GENERALADDRESS.ADLINE1,
GENERALADDRESS.ADLINE2,
GENERALADDRESS.CITY,
GENERALADDRESS.ST,
GENERALADDRESS.ZIP,
VIEWLIFESTYLETYPES.GP_ID,
VIEWLIFESTYLETYPES.LSR_TYPE_DESC,
VIEWLIFESTYLEPROGRAMS.GPID,
VIEWLIFESTYLEPROGRAMS.LSRPGMNM,
VIEWLIFESTYLEPROGRAMS.LSRPGMTYP,
GRPASSOC.CLID,
GRPASSOC.ASSOCNUMBER,
GRPASSOC.ASSOCIATIONNM
From WORK.VIEWLIFESTYLEREPORT/*,WORK.GROUPCLH*/
Left join WORK.VIEWLIFESTYLEGROUPS
On VIEWLIFESTYLEREPORT.GP_N = VIEWLIFESTYLEGROUPS.GROUP_NUMBER
Left join WORK.CLIENTMGR
On VIEWLIFESTYLEREPORT.GP_ID = CLIENTMGR.CL_ID
Left join WORK.ASM
On VIEWLIFESTYLEREPORT.GP_ID = ASM.CL_ID
Left join WORK.VIEWLIFESTYLEWORKLOAD
On VIEWLIFESTYLEREPORT.GP_ID = VIEWLIFESTYLEWORKLOAD.GROUP_ID
Left join WORK.GENERALADDRESS
On VIEWLIFESTYLEREPORT.GP_ID = GENERALADDRESS.CLID
Left join WORK.VIEWLIFESTYLETYPES
On VIEWLIFESTYLEREPORT.GP_ID = VIEWLIFESTYLETYPES.GP_ID
Left join WORK.VIEWLIFESTYLEPROGRAMS
On VIEWLIFESTYLEREPORT.GP_ID = VIEWLIFESTYLEPROGRAMS.GPID
Left join WORK.GRPASSOC
On VIEWLIFESTYLEREPORT.GP_ID = GRPASSOC.CLID
/*Where VIEWLIFESTYLEREPORT.GP_ID = GROUPCLH.CL_ID*/
Where VIEWLIFESTYLEWORKLOAD.PRODUCT_DESCRIPTION <> 'Supplemental Only';
quit;
Message was edited by: lloraine